Text and number Data to be parsed ( extracted ) from a single column

[tudorr]

Hello there
I have a difficult task to complete and I kindly ask for your help.
I need to split in Excel some thousands of data rows containing text and numbers information of different character length
Data to be extracted ( parsed) is stored in a single column( the first one) and needed results look like this ( see attach.):

Data to be extracted ( see attach) :
Green Street no 16 Smart town Schmit Anders +3900098787867

Needed results:
1. B column named Address - result is Green Street no 16 Smart town G
2. C column named persons name : Schmit Anders
3. D column named telephone no: +3900098787867
Other data ex:

Red bd 17 Glasgow John "Redhead"Smith 4404987789
Light str 34 Central Bulding Curchill Plein Down Town Eddie Cohen 1325541987654

…. Thousands of data rows

Data is composed by addresses, name of persons and phone numbers.
Person's name is has two or three parts, address and phone numbers length is different ( 9 to 12 characters)
I need to complete this difficult task (for me, obviously, ) in Excel using a combination of functions and/or multiple operations.
Thank you very much for any help and attention

Tudor

[Cullen8]

Hi Tudor,

I'm sure there's a better way to do this but if you are in a crunch you could use the below formula to grab everything to the right of the last space found? You could get your phone numbers first, then back into the name the same way by updating your data (i.e removing the phone number from the column once you get it)?

Please Login or Register  to view this content.

hope this helps,
Cullen

[Pete_UK]

This will get you the phone number in D2:

=MID(TRIM(A2),1+FIND("~",SUBSTITUTE(TRIM(A2)," ","~",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ","")))),255)

but I think your data is too variable to extract the address and name reliably.

Hope this helps.

Pete

[tudorr]

That really helps Pete! thank you

[Cutter]

If you are satisfied with the solution(s) provided, please remember to mark your thread as Solved.

New quick method:
Select Thread Tools (above first post on page) -> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
Go to the first post
Click edit
Click Go Advanced
Just below the word "Title" you will see a dropdown with the words "No prefix".
Change to "Solved"
Click Save

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[tudorr]

I was waiting , maybe, for a complete solution. If no other answer....i`ll mark the thread solved, surely. thanks for understanding

[Pete_UK]

You could use this formula:

=SUBSTITUTE(TRIM(A2)," "&D2,"")

to remove the phone number and final space from A2, so that basically you just need to use LEFT(... and RIGHT(... on this to get the address and name. But, from what you have said (names can be two or three words or variable length, address can have variable units and be of variable length, etc) then how can the formula work out where to split them?

Pete

[tudorr]

I`ll use your formula. thank you again, Pete