[Solved] How do I split field text at specific char ?

[mOrloff]

A couple spreadsheets of customer info have been sent my way.
The entire address for each row is all in one field.
I need to break that address field into multiple columns.
Fortunately, at least there's a delimiter ... there are semicolons (;) at the break points.

EXAMPLE:
I need to take
- "1234 ELIZABETH DR; SOMETOWN MA, 01234; USA;"
and break it into
- "1234 ELIZABETH DR" "SOMETOWN MA, 01234" "USA"
(even better would be to break the city state, zip into 3 columns too, but I'll take what I can get )
NOTE: Some records have PO Boxes rather than street addresses ... if that matters


I've googled around a bit, and I feel like I'm on the right track .. I'm just a bit stuck.
To break out the first section, I'm currently using

Code: Select all

=LEFT(C2,FIND(";",C2)-1)

And that seems to be working great.

I'm running into trouble when I try to get the next section.
I'm starting with

Code: Select all

=RIGHT(C2,FIND(";",C2))

And already I'm getting loosing ground
.. there is a seemingly-random quantity of char's that get truncated from the left side of the resulting string.
IE, for our example, it returns "N MA, 01234; USA;"

Tips ?
Pointers ?
Secrets of the universe ?
~ Mo

[crusader]

Try: Data -> Text to Columns

[mOrloff]

Well well well, wasn't that easy
Thanks.

[crusader]

Thanks.

You are welcome: glad to help...

[mOrloff]

Is there a way to split by the last incidence of a char ????

Data -> Text to Columns worked GREAT.
I even used it again to split at the comma and separate the ZIP out of the CityStateZip column.
Now I've got a column of cityState info, and I'm looking for a good way to split that further.
Since some city names contain multiple spaces, and all that separates state value is just another space, Data -> Text to Columns doesn't quite seem the tool for the job.

Is there a way to split at the last occurrence of a space ??
(I can hack the state out with RIGHT(D197,2), but then I have to continue down the hack-path and find some way to get a clean city value)

[crusader]

Is there a way to split at the last occurrence of a space ??
(I can hack the state out with RIGHT(D197,2), but then I have to continue down the hack-path and find some way to get a clean city value)

Using RIGHT(D197;2), you can split the state to another column. Use =SUBSTITUTE(D197;RIGHT(D197;3);"") to remove the space after the city as well as the state abbreviation (I am assuming that is what you mean by a "clean city value").

Hope this helps...

[mOrloff]

That is exactly what I meant by "clean city value".
Worked perfectly.
Thanks-a-bunch

[crusader]

That is exactly what I meant by "clean city value".
Worked perfectly.
Thanks-a-bunch

Thank you for the feedback.

[wpomier]

Nowadays REGEX function can be used in that case.
Example:
A1: "1234 elizabeth dr; sometown ma, 1234; usa"
If B1 is =REGEX(A1, "[^;]+", ,1) 'B1 will be "1234 elizabeth dr"
If B1 is =REGEX(A1, "[^;]+", ,2) 'B1 will be " sometown ma, 1234"
If B1 is =REGEX(A1, "[^;]+", ,3) 'B1 will be " usa"

Use TRIM( ) to remove the start/end spaces

[robleyd]

For the benefit of OpenOffice Calc users who may find this topic, as at the time of writing AOO Calc does not have a REGEX() function.

[Lupp]

Nowadays REGEX function can be used in that case.
Example:
A1: "1234 elizabeth dr; sometown ma, 1234; usa"
If B1 is =REGEX(A1, "[^;]+", ,1) 'B1 will be "1234 elizabeth dr"
If B1 is =REGEX(A1, "[^;]+", ,2) 'B1 will be " sometown ma, 1234"
If B1 is =REGEX(A1, "[^;]+", ,3) 'B1 will be " usa"

Use TRIM( ) to remove the start/end spaces

Anybody who wonders at the comma as parameter separator in formulas may find some information in https://ask.libreoffice.org/en/question ... -my-sheet/ , and may consider my plea there.

It's one of the few advantages of AOO over LibO now that it still insists on a parameter separator allowing for global exchange of formulas whithout special measures.