Cleaning up data

[kitty77]

I have some data that was imported. Kind of messy...

It comes in like this:
10.9 78.5 386.54 <1 <1 18.8 / -68.3 <1 ND ND

I would like to have it look like this:
10.9
78.5
386.54
<1
<1
18.8 / -68.3
<1
ND
ND

So, I would like to use a command button and remove the extra spaces and return to a new line for each value.

Thanks

 

[June7]

So that is one long string of text imported into a single field? I expect this will require VBA to parse string into an array object then loop through array to write records to table. However, what you show as final result is a bunch of unrelated records. Should these records maintain association? What would be the key value? Surely there is some other info with the import?

 

[kitty77]

Yes, that is just one field. It is coming from an extract program from a pdf. Lots of pdf's works pretty good but it lumps some data in one field. So, I still want it in one field just formatted. It has an id field that could be linked to the record.

 

[June7]

If you still want in one field but on separate "lines", replace space with CR and LF. Expression with Replace() can do that, however, complication is value with " / " separator.

Simplest approach would be to replace the " / " with "/" or ":".

Replace(Replace([fieldname], " / ", "/"), " ", Chr(13) & Chr(10)))

Use expression in query or textbox and original data is unchanged.

 

[theDBguy]

Yes, that is just one field. It is coming from an extract program from a pdf. Lots of pdf's works pretty good but it lumps some data in one field. So, I still want it in one field just formatted. It has an id field that could be linked to the record.

Hi. Have you tried using the Replace() function in an UPDATE query.

Edit: Ah, too slow. Sorry for the duplicate information.

 

[CJ_London]

taking June's code you can put the spaces back again around the / by using an additional replace

Replace(Replace(Replace([fieldname], " / ", "/"), " ", Chr(13) & Chr(10)),"/"," / ")

 

[kitty77]

I'm a bit confused... Ok, let's replace the spaces with a : and leave the space before and after the /. Is that possible?

 

[kitty77]

I get a compile error when with this...

Replace(Replace(Replace([fieldname], " / ", "/"), " ", Chr(13) & Chr(10)),"/"," / ")

I put it in a command button.

 

[kitty77]

I think a simple find and replace will solve me problem. All I need is to find the : and replace it with a return. Any idea how that would look.

 

[kitty77]

Basically I'm using the find and replace feature. I just need to know how to make it find the : and replace it with a return. Can't seem to find how to do that.

 

[CJ_London]

I get a compile error when with this...

Replace(Replace(Replace([fieldname], " / ", "/"), " ", Chr(13) & Chr(10)),"/"," / ")

I put it in a command button.

two things

if you are running this from a button in a form, you need to 1) replace [fieldname] with the name of your control and 2) you need to assign it back to the control so the code would be something like

txtSomeName=Replace(Replace(Replace(txtSomeName, " / ", "/"), " ", Chr(13) & Chr(10)),"/"," / ")

 

[June7]

If you replace spaces with : then the / will end up as :/:. Doesn't really help.

If you are using Find/Replace dialog, then you want to change original data? Using Find/Replace to replace with non-printing characters can be a little tricky.

If you want to change original data, can be done with an UPDATE action SQL. But still need to get nested Replace() working.

 

[kitty77]

Yes, but I can go back and replace the :/: with " / " and that would be just fine.
But how can I do that with the replace?

 

[kitty77]

If I can't with replace, then I just need to know how to replace the ":" with a new line or return. With a command button.

 

[kitty77]

I'm trying this: Replace([Field15],Chr$(13) & Chr$(10),":")

But it's giving me an error... Syntax error

 

[June7]

You have the arguments backwards.

But you will end up with:

18.8
/
-68.3

The triple nested Replace examples should work.

 

[kitty77]

I figured it out. Thanks to everyone for the help!!