Cleaning up data (1 Viewer)

kitty77 · Sep 19, 2019

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 · Sep 19, 2019

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 · Sep 19, 2019

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 · Sep 19, 2019

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 · Sep 19, 2019

kitty77 said:
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 · Sep 19, 2019

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 · Sep 19, 2019

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 · Sep 19, 2019

I get a compile error when with this...

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

I put it in a command button.

kitty77 · Sep 19, 2019

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 · Sep 19, 2019

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 · Sep 19, 2019

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 · Sep 19, 2019

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 · Sep 20, 2019

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 · Sep 20, 2019

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 · Sep 20, 2019

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

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

June7 · Sep 20, 2019

You have the arguments backwards.

But you will end up with:

18.8
/
-68.3

The triple nested Replace examples should work.

kitty77 · Sep 20, 2019

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

Cleaning up data (1 Viewer)

kitty77

Registered User.

June7

AWF VIP

kitty77

Registered User.

June7

AWF VIP

theDBguy

I’m here to help

CJ_London

Super Moderator

kitty77

Registered User.

kitty77

Registered User.

kitty77

Registered User.

kitty77

Registered User.

CJ_London

Super Moderator

June7

AWF VIP

kitty77

Registered User.

kitty77

Registered User.

kitty77

Registered User.

June7

AWF VIP

kitty77

Registered User.

Similar threads

Users who are viewing this thread