Is anyone able to tell me how to extract the last 2 No. 5 or 6 digit numbers from strings such as these please?
I need to convert
Cell B1 = Basingstoke,51.26654,-1.092396,463418,152322
Cell B2 = Bournemouth East,50.719164,-1.880769,408513,91066
to
cell C1 = 463418
cell D1 =152322
cell C2 = 408513
cell D2 = 91066
Thanks in anticipation
Try Data - text to columns - Use comma as delimiter. In the last window of the wizard choose NOT to import the 3 first columns ( or delete them manually if you prefer)
Or use formula
in C1 and pull formula to the right and down until you see blanks
=TRIM(MID(SUBSTITUTE($B1,",",REPT(" ",125)),125*(LEN($B1)-LEN(SUBSTITUTE($B1,",",""))+COLUMNS($A:A)-2),125))
Updated formula to insure there will be always extracted only the last two groups of digits in case the size of the string changes.
Row\Col Basingstoke,51.26654,-1.092396,463418,152322 463418 152322 Bournemouth East,50.719164,-1.880769,408513,91066 408513 91066 Bournemouth East,50.719164,-1.880769,408513,91067,555555,666666 555555 666666
Last edited by AlKey; 02-28-2015 at 01:19 PM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
try also
in c1 drag down
Formula:
=TRIM(RIGHT(SUBSTITUTE(B1,",",REPT(" ",LEN(B1))),LEN(B1)))
and in d1
Formula:=TRIM(LEFT(RIGHT(SUBSTITUTE(B1,",",REPT(" ",100)),200),100))
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
In C1 enter and fill down:
Formula:=IFERROR(--TRIM(LEFT(RIGHT(SUBSTITUTE(B1,",",REPT(" ",LEN(B1))),LEN(B1)*2),LEN(B1))),"")
In D1 enter and fill down:
Formula:=IFERROR(--TRIM(RIGHT(SUBSTITUTE(B1,",",REPT(" ",LEN(B1))),LEN(B1))),"")
Result is real numbers and not text numbers:
Basingstoke,51.26654,-1.092396,463418,152322 Bournemouth East,50.719164,-1.880769,408513,91066
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Ian99099 
Register To Reply
Bookmarks