Extracting Multiple types of Text Characters from Text Strings

[DannyGIS]

Please forgive me if this is a Formula type question but I'd like to think its not.

I am using MS Excel 2013, and am doing this in an .xls spreadsheet.

I have a field that has text strings (1-7 characters) of multiple lengths (1-7 characters) which are a combination of letters and numbers. In certain records the text string begins with a letter, which I need to extract and put in another field. In certain records the string ends with a letter, which I need to put in another field as well.

What I would like to know is what Text Formulas do I need to use to extract the Letter at the beginning for the field and /or the end of the string?

******************

I know this sounds like a "If String in Cell X at Character 1 position, has Letter, then Return letter, Else nothing" for the Begin field, and something like that for the End field, but there doesn't seem to be a place where I can do that.

I have tried using DeLimiters abut the results were too variable (clarification provided below).

I have used the MID and LEFT functions (Field, character position, number of characters) and they work, but I have to do editing of the results so I can get the strings of numbers without the text and vice versa.

This is the bottom line:

For Begin: If string begins with Text Character, return Text, Else Nothing.


For End: If String has character, return Character, Else nothing.


Also, I can even name the specific Letters that I need to put in the Beginning and Ending fields, and I have a feeling that could be done with the Character Key, and I tried looking, but I don't know where the listing of the Character Keys are...

[Pete_UK]

You could do something like this for the start string:

=IF(ISNUMBER(LEFT(A1)*1),"",LEFT(A1))

and

=IF(ISNUMBER(RIGHT(A1)*1),"",RIGHT(A1))

for the end string, then copy down as required.

Hope this helps.

Pete

[DannyGIS]

Pete,

Yes, those two helped big time and then some.

Now, the other part of this is how do I get just the "number" characters out of the string?

I will do what I can to work with the command (alter it here and there) to see if that works.

I do remember some of what you are doing from another type of dialogue I had on this same page a couple of years ago in terms of "If Text in Cell X to the left of position 1, then ... else ...."

But the part that's tricky is "IF Number to Right of position X, then return the COMPLETE number string, not just the first number, AND without Text on the end".

Maybe its a matter of specifying how many characters that are numbers after position X?

Thanks again,

Dan

[Pete_UK]

If the original text string may (or may not) contain one letter at the beginning and one letter at the end, then you have already found those letters with the formulae that I have already given you (assume those are in B1 and C1 respectively).

So, you can get the remaining digits by removing those characters using the SUBSTITUTE function, like this in D1:

=--SUBSTITUTE(SUBSTITUTE(A1,B1,""),C1,"")

Just copy down as far as you need to. The -- at the beginning of the formula convert the text values of the digits into a proper number - you can omit them if the text values are what you want (i.e. if you don't need to do any arithmetic on them).

Hope this helps.

Pete

[Tony Valko]

How about posting SEVERAL representative examples and show us what results you expect.

[DannyGIS]

And your last name is ....Townshend...Daltrey???

Pete,

Good times. Big time good times. Thanks again and again.

Now: can I ask where you learned this type of stuff? I actually even brought a bunch of SQL papers from a class that used it a couple ears ago but they were more Oracle-centric. I have used MS Excel as long as I've been in GIS, but I haven't been keeping up on Querying type stuff like this. Is there a real good website for this?

Daniel B.

[DannyGIS]

Pete's formulas did the trick. Create a dummy spreadsheet and substitute (!) Cell References and it came together.

EXAMPLE: COLUMN A COLUMN B COLUMN C

N27.054 N 27.054

T101.435R T 101.435 R

5 5

3L 3 L

[Pete_UK]

This forum is a real good website for learning about Excel - just browse around the various posts and you are sure to pick some things up.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[DannyGIS]

Pete,

Thanks for all of that. I did a) click on the star and gave props to you, b) clicked on star above (ooo!) and indicated Thread is Solved, and c) will do what I can to look at site and see what there is.

Dan

[Pete_UK]

Thanks, Dan - we'll be seeing a lot more of you , then.

Pete