Log In
Register
Home
June 24, 2018
Offline
Hi,
I am working on a text function project for work. I need to format information regarding the person's first name, middle name, and last name. Also, need to extract the street address, street name, city, state, and zip code. These are all in one cell and I need to break them apart. Is this possible to do. When you open the file it is on the first tab. Thanks for your help....Joe from Michigan.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
’There is no file attached. Do check out these articles, they might give you the needed start.
https://www.myonlinetraininghu.....d-function
https://www.myonlinetraininghu.....el-formula
https://www.myonlinetraininghu.....to-columns
June 24, 2018
Offline
Hi Eskilstuna,
Here is the file, I apologize, I thought it was uploaded. Can you take another look at it? Thank you. Joseph
Active Member
September 1, 2014
Offline
Have the text split at 2 different seperators. And this in 2 steps.
The first seperation would 'text to column' by splitting on the comma
Than see the result and what it will show is the State with ZIP code is now in the adjacent cell
When looking at the remainder of the text there appears a number (house number) which is placed after the name
Here you can replace the blank between the Sur Name and the Number with a comma
| REPLACE(old_text, start_num, num_chars, new_text) |
The position of the above mentioned blank between Sur Name and House Number needs to be located ( n th position )
=(MIN(FIND.ALL({0;1;2;3;4;5;6;7;8;9};A13&"0123456789"))) [ in column "C"]
Now the formula gives you a position, say with Marcia Jane Parker, that would be 20. Reduce this by 1 (i.e. +A13-1) [in column "D"]
The last formula would give you the text with a comma, which can enable the result to be seperated by text to column
=REPLACE(A13,D13,1,",")
You now can tidy the string into the correct sequence. (Or combine the formulaes into 1 lengthy formulae)
VIP
Trusted Members
December 7, 2016
Offline
Hello Joseph,
I notice that you have different delimeters, spaces and commas. That makes it difficult to maintain text such as street adress etc. If it is possible to have one and same delimiter, for example a comma, it will be a lot easier to get things correct.
But you have come to a good start in your sample file. Just shout out if there is anything specific you need assistance with.
Br,
Anders
June 24, 2018
Offline
Hi Anders,
Thanks for responding. I got another piece of my project which was extracting the street number and name. However, I do have a question regarding the start number and number of characters. I have a long list of names so I was wondering if there is a way to do that differently. In the last name I had to adjust each start number and number of characters. That would take too long to do. Any suggestions?
Also, I'm stuck on getting the city and state out. I put the delimeter the same to separate as a hyphen. Thanks for you help....
VIP
Trusted Members
June 25, 2016
Offline
Hi Joseph
When splitting names and addresses, is very difficult to get them 100% correct due to the different formats.
I have attempted to split them using some confusing formulas but will work provided the full address follows a certain format (refer attachment)
Hope this will give you some ideas.
Good luck.
Sunny
June 24, 2018
Offline
Hi Sunny,
Thanks for responding to my request. Yes, that is a little bit confusing, I'm not sure how that all works. Will those formulas work if I follow your suggestions for another spreadsheet? Thanks, Joe.
VIP
Trusted Members
June 25, 2016
Offline
Hi Joseph
I don't see any problem with that. You only need to change the range.
In most conversions, I would be extremely happy to get 80% correct.
As for the other 20%, I will segregate and convert them using other methods,formulas or manually depending on their formats.
Let us know if you need any help.
Sunny
1 Guest(s)
