Formula Extract Characters at the Beginning of a String

D

Daveydoodles

Board Regular

Joined

May 21, 2013

Messages

215

• May 22, 2014

• #2

Your formula works for me when I try it with the examples you posted, including the 127...

 

Upvote 0

P

pgc01

MrExcel MVP

Joined

Apr 25, 2006

Messages

19,897

• May 22, 2014

• #3

Hi
Welcome to the board

Your formula works for me too. If you always have 14 characters after the digits your formula seems OK.

 

Upvote 0

B

billszysz

Active Member

Joined

Feb 26, 2014

Messages

384

Office Version

1. 365

• May 22, 2014

• #4

Hi,
If not always 14 characters after number try this
LOOKUP(2,1/--LEFT(A1,ROW($1:$3)),--LEFT(A1,ROW($1:$3)))
or if your string can begin with letter
=IFERROR(LOOKUP(2,1/--LEFT(A1,ROW($1:$3)),--LEFT(A1,ROW($1:$3))),"")

 

Upvote 0

G

gonesalsa

New Member

Joined

May 5, 2014

Messages

7

• May 22, 2014

• #5

Thanks billszysz. That works. Can you explain what the first formula is doing? I want understand the formula, instead of blindly using it.

 

Upvote 0

P

pgc01

MrExcel MVP

Joined

Apr 25, 2006

Messages

19,897

• May 22, 2014

• #6

If your string starts with a number of up to 4 digits, try:

=-LOOKUP(1,-LEFT(A1,{1,2,3,4}))

 

Upvote 0

B

billszysz

Active Member

Joined

Feb 26, 2014

Messages

384

Office Version

1. 365

• May 22, 2014

• #7

gonesalsa,
My english is to bad to do this (it is not na native language) So this is a little tricky.
ok... your second record is 10EXXXXXXXXX1204 so value from --LEFT(A2,ROW($1:$3)) are {1,10,#ARG!}

1/--LEFT(A2,ROW($1:$3)) gives us {1,0.1,#ARG!}

Lookup is searching 2 in vector above (but 2 is never there) and errors are ommited. If Lookup don't find 2 (always) then give us the last value in vector (if exist) - in this case 0.1
And second vector --LEFT(A2,ROW($1:$3)) has at the same position value 10 {1,10,#ARG!} so lookup give us 10

I don't know if my explanation is undertandable but i can't better...so sorry if it is insufficient (

You can replace ROW($1:$3) by array constant {1,2,3} - if you want more than 3 digits you can simply change this array (i.e for 5 digits {1,2,3,4,5})

 

Upvote 0

B

billszysz

Active Member

Joined

Feb 26, 2014

Messages

384

Office Version

1. 365

• May 22, 2014

• #8

Your formula is better and easier than my. Thanks

 

Upvote 0

B

billszysz

Active Member

Joined

Feb 26, 2014

Messages

384

Office Version

1. 365

• May 22, 2014

• #9

We should be careful in case of more than 3 digit at the beginning.
Why?.... Try this string 2SEPXXXXX3210

 

Upvote 0

P

pgc01

MrExcel MVP

Joined

Apr 25, 2006

Messages

19,897

• May 23, 2014

• #10

billszysz said:

We should be careful in case of more than 3 digit at the beginning.
Why?.... Try this string 2SEPXXXXX3210

Click to expand...

Hi Bill

You are right. This formula tries to convert a string to a number. 2SEP is some languages will be interpreted as 2 September and will therefore give you a wrong result.
Notice that it does not even have to be more than 3, 3 is enough. Try 1E2ABC.

Conclusion: these are simple formula that will usually work for most cases.
If you want to be sure that you're really getting the digits at the beginning of a string you have to test them one by one, not in bulk like with Left().

 

Upvote 0