• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help extracting numbers from a string that has mix of numbers and letters.

K

kulmansam

New Member
My data looks like this

Cell A1: W144.8 N82.11 Extract==> Cell B1: 144.8 Cell C1: 82.11

Cell A2: W23 N278.5 Extract==> Cell B2: 23 Cell C2: 278.5

Cell A3: W123.4 N223 Extract==> Cell B3: 123.4 Cell C3: 223


After going through the posts. I was impressed by several answers.

Following answer, I know I can use in column C, to exract outer number values from Column A

=LOOKUP(9E+300,RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))+0)


I need formula to put in column B to extract inner number values from Column A.


Any help would be greatly appreciated.


Kind regards

Kulmansam
 
Hi, kulmansam!


Type this formula in B1 and copy to C1 and down as required:

=VALOR(DERECHA(EXTRAE($A1&" ";SIGNO(COLUMNA()-2)*(HALLAR(" ";$A1&" "))+1;HALLAR(" ";$A1&" ";SIGNO(COLUMNA()-2)*(HALLAR(" ";$A1&" "))+1)-(SIGNO(COLUMNA()-2)*(HALLAR(" ";$A1&" "))+1));LARGO(EXTRAE($A1&" ";SIGNO(COLUMNA()-2)*(HALLAR(" ";$A1&" "))+1;HALLAR(" ";$A1&" ";SIGNO(COLUMNA()-2)*(HALLAR(" ";$A1&" "))+1)-(SIGNO(COLUMNA()-2)*(HALLAR(" ";$A1&" "))+1)))-1)) ----->

in english: =VALUE(RIGHT(MID($A1&" ",SIGN(COLUMN()-2)*(SEARCH(" ",$A1&" "))+1,SEARCH(" ",$A1&" ",SIGN(COLUMN()-2)*(SEARCH(" ",$A1&" "))+1)-(SIGN(COLUMN()-2)*(SEARCH(" ",$A1&" "))+1)),LEN(MID($A1&" ",SIGN(COLUMN()-2)*(SEARCH(" ",$A1&" "))+1,SEARCH(" ",$A1&" ",SIGN(COLUMN()-2)*(SEARCH(" ",$A1&" "))+1)-(SIGN(COLUMN()-2)*(SEARCH(" ",$A1&" "))+1)))-1))


And don't get lost with the waypoints, the first one is in the middle of the Artic if my GPS works fine... go home directly :)


Regards!
 
@Others

A similar thread is here, for reference. Might get some good ideas:

http://chandoo.org/forums/topic/extract-all-the-numeric-values-from-the-right-of-any-alphanumeric-string


@kulmansam

First, thanks for starting a new thread. Much appreciated. =)
 
@SirJB7

Your formula appears to do exactly the same as what is already found, the final number (although in a lot more steps). Need a way to get internal number.
 
Hi, kulmansam!


Just in case, here's the test file:

https://dl.dropboxusercontent.com/u/60558749/Need%20help%20extracting%20numbers%20from%20a%20string%20that%20has%20mix%20of%20numbers%20and%20letters.%20%28for%20kulmansam%20at%20chandoo.org%29.xlsx


Column A : waypoints

Column B : longitude

Column C : latitude

Columns D:N : detailed formula steps (except F, in pairs for 1st and 2nd values)


D:E : columns B:C

=COLUMNA()-2 -----> in english: =COLUMN()-2


F : lenght of A+1

=LARGO($A2&" ") -----> in english: =LEN($A2&" ")


G:H : from

=SIGNO(D2-2)*(HALLAR(" ";$A2&" "))+1 -----> in english: =SIGN(D2-2)*(SEARCH(" ",$A2&" "))+1


I:J : to

=HALLAR(" ";$A2&" ";SIGNO(D2-2)*(HALLAR(" ";$A2&" "))+1) -----> =SEARCH(" ",$A2&" ",SIGN(D2-2)*(SEARCH(" ",$A2&" "))+1)


K:L : chunks

=EXTRAE($A2&" ";SIGNO(D2-2)*(HALLAR(" ";$A2&" "))+1;I2-(SIGNO(D2-2)*(HALLAR(" ";$A2&" "))+1)) -----> in english: =MID($A2&" ",SIGN(D2-2)*(SEARCH(" ",$A2&" "))+1,I2-(SIGN(D2-2)*(SEARCH(" ",$A2&" "))+1))


M:N : longitude & latitude (same as B:C)

=VALOR(DERECHA(K2;LARGO(K2)-1)) -----> in english: =VALUE(RIGHT(K2,LEN(K2)-1))


Regards!
 
@Luke M

Hi!

It extracts in column B the longitude (number of first coordinate) and in column C the latitude (number of second coordinate).

To do that it uses (COLUMN()-2) for retrieving a 0 (zero) or a 1 (one) for seldom columns B and C, and with it extract data from 1 to 1st space for column B and from 1st space + 1 to 2nd space for column C. Please check if with the posted formulas it's a little more clear (as I know you suffer from the CASFFML serious illness).

Regards!

PS: I need a Carlsberg... b(ut)ob(ut)hc!

PS2: "Luke M needs 2!" (b(ut)ob(ut)hc dixit, if he were here) :)
 
LOL thanks for the quick responses. Those are OLD OLD longitudes and latitudes. They do not even respond to real compass. They mark cemetery plots from one corner to another. By the time I am done helping them. They will have real co-ordinates.

I was a way for a couple of minutes. I will try this now.

Thanks
 
Good day Kulmansahe


"cemetery plots" don't let the visitor see the spread sheet, they will want a formula in the plot cell to double the value of the will.
 
Hi


Try the following.

In B1:=LOOKUP(20^20,1*MID(A1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1))))) and copy down.


Kevin
 
Hi, kulmansam!

Please check if it works fine for both coordinates; if not, just advise if any issue.

Regards!
 
I apologize in my orininal post I did not specify that data is not from real co-ordinates. The original value went from 0,0, and went exactly 4 feet west to mark next grave or 6 feet north to mark next grave. Of course this formula did not work well. So you see the .5 etc. Because some graves did not co-operate, or should I say some grave diggers did not co-operate, so some grave rows run in straight line and right between graves there you will find a new row begin.


SirJB7, thank you so much for your help.


KEVIN!!! Thank you.. works like a charm!

I'm using your formula.
 
@ BOBHC. I created what I believe to be first database on line for a cemetary plot about 13 years ago. I built them a website etc. Purpose was for visitors from out of town visiting this cemetery from 1900's, would be able to tell before driving from far off place whether the person/deceased they were looking for was at this cemetery or not.

www.guedrycemetery.com

I still help them. But it's time for correcting all the human error (I did not lock some fields, so they messed up) and updating the database program for the operators and website visitors alike.
 
Hi, kulmansam!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: If I were you I'd use Kevin@Radstock's formula too.


@Kevin@Radstock

Hi!

Smart approach, very nice formula. Copied to stock (w/o permission).

Regards!
 
Hi Sam,


Just another approach..


Code: 
=SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",99)),(COLUMN(A:J)-1)*99+1,99)),"W",""),"N","")*1


Drag Right Or Down..


Regards,

Deb
 
@Debraj Roy

Hi!

Will be keeping this.

Regards!


EDITED


PS: As waypoints there might be E as well as W and S as well as N... :)
 
Debra

Thank you so much. I have already done the conversion. Then I copied and pasted as 'value' on same column. It is a one time thing. Now I am going to lock all the cells and I won't have the problem in the future.

Kind regards

kulmansam
 
Good day kulmansam


First, I hope my grave yard humor was not offensive, none intended.


Secondly, I have looked at your site and although it may look a bit plain it has its own quite charm and not to much flash bang wallop, just what is needed.


Thirdly,Thanks for taking the time to say thank you to those that helped you, all to often the forum members but forward solutions and then hear no more, so they are left in limbo wondering whether their solution has done what the OP wanted. I know those that helped you will appreciate your thanks.
 
BOBHC, not at all... no offence.

Yes the cemetery website is plain... believe it or not, nothing has changed much since 2001 on that website! (so if you think about it, back in the day to have a website like that with searchable database, interactive maps etc, it was a big thing, today hmmm... not so). Main changes were on the database and a few very very minor updates.


It's time for overhaul. I just like the marble look that kind of goes well with marble tomb stones. But I am planning to have a website similar to www.dealerelements.com, I do not have to update mobile site. One site will format for all devices.


I do not use flash coz I realize a lot of people use ios devices from Apple, and they would not benefit much.

Thank you for taking time to visit the website and kind comments.


As usual I am appreciative of any help from any one. Today it's me, tomorrow it's you. If I can be of help, I would be honored.

Havea great weekend everyone.
 
@ kulmans Good evening


I think the problem with a lot of websites is that they try to put to much information on the page, the human eye/mind can only take so much in and site builders can run the risk of information overload and that is when viewers either miss what is posted or they just give in and leave the site.


In your case I think the main consideration is compassion. If your site visitors are trying to find the location of a loved ones grave the last thing they want is a flashy site.


But I would be interested in viewing the new site so please let us know when you have finished the update.
 
@b(ut)ob(ut)hc

Hi, my friend!

Good afternoon.

Particularly in my case even if I struggle I can't find any reason to prevent you or anybody else from visiting a cemetery before me. Just when saw the white light, come back and tell me how did it looked like at the other side. I still prefer to stay in ignorace about it.

But come back, don't stay much long there!

Regards!


@kulmansam

Hi!

Joke, nothing regarding your website. Just in case.

Regards!
 
You must log in or register to reply here.
Back
Top