Finding a position in a Cell using a formula

[jxbeeman]

Hi,
I'm trying to find a certain data type in a cell and the position it is in.

For example I have a text cell with the following in it "SomePartName
12345 ".

How would i be able to find the first number position in this cell using a
formula?
How would i be able to find the last number position in this cell using a
formula?

The reason i'm looking for something like this is to be able to separate
strings of text which contain the parts name and then a number after it along
with spaces inbetween.

Thanks for the help.
Josh

 

[macropod]

Hi jxbeeman,

The following formula will return the position of the 1st digit in a string in A1, 0 otherwise:
=IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))>LEN(A1),0,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))
You should be able to combine that with the LEFT, RIGHT & LEN functions to split the string into its alpha and numeric parts.

 

[JLatham]

Take a look at this discussion:
http://www.microsoft.com/office/com...misc&mid=2d21d74d-cdbc-4064-828f-44a6fb3115aa
where I offered a way to find the 1st digit in a text string. Finding the
last one is tougher and since you say that there may be spaces within the
numbers?? could be something better served with a User Defined Function.

 

[Jacob Skaria]

Another one to find the first position...

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))

If this post helps click Yes

 

[fake_be]

Hi Josh,


My 2 cent try.

Use the text to columns function for the columm with a space " ". Now
you have everthing in multiple cells on the same row. Search the
textvalue with =IF(ISNUMBER(A1)=TRUE;A1;"") or ISTEXT function for all
the new columms etc...

Or you could do a find an replace of 10spaces => 1 space; 9 spaces =>
1 spaces, 8 spaces => 1 spaces, ... 2 spaces => 1 spaces.
Then use the FIND function of the space with a formula like =LEFT
(A1;FIND(" ";A1;1)) for the text value and =RIGHT ect... for the
numeric value.

Looking forward for a simpeler solution of an expert.
Tom





Probable VBA would work better but cant help you with that.

 

[jxbeeman]

What is the reason for the "0123456789" at the end --> A1&"0123456789"
Thanks,
Josh

 

[Ron Rosenfeld]

Hi,
I'm trying to find a certain data type in a cell and the position it is in.

For example I have a text cell with the following in it "SomePartName
12345 ".

How would i be able to find the first number position in this cell using a
formula?
How would i be able to find the last number position in this cell using a
formula?

The reason i'm looking for something like this is to be able to separate
strings of text which contain the parts name and then a number after it along
with spaces inbetween.

Thanks for the help.
Josh

If you post some samples of your data, and your expected results, it will
probably be possible to offer a more efficient method of accomplishing your
goal.
--ron

 

[Jacob Skaria]

Dear "jxbeeman"

I dont think I am good in explaining things..But still I will give it a try.
The basic functionality of Search function is to return the first position of
a find item.

Here the search function will return the positions of each entry in the find
text which is {0,1,2,3,4,5,6,7,8,9}. Try the below formula which do not refer
any cell

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},"0123456789"))

Search returns all positions in the text string "0123456789"
Position of 0 = 1
Position of 1 = 2
Position of 9 = 10
and so the minimum value is 1

Now try with a different find string (note that 0 is replaced with 9 within
FIND)
=MIN(SEARCH({9,1,2,3,4,5,6,7,8,9},"0123456789"))
Position of 9 = 10
Position of 1 = 2
Position of 2 = 3
and so the minimum value is 2

Remember the return values are positions.

A1 = "TEST" (without numerics)
Now when you have A1 & "0123456789" SEARCH will still return the positions

"test012456789"
0 will be in position 5
1 will be in position 6
and so on....

A1 = "T123EST" (with numerics)
Now when you have A1 & "0123456789" SEARCH will still return the positions

"t123est012456789"
0 will be in position 8
1 will be in position 2
2 will be in position 3
4 will be in position 11
and so on....

The minimum of the return values is 2 which is were the first numeric is the
text.


If this post helps click Yes

 

[David Biddulph]

Have you tried what happens without it? You'll get a #VALUE! error unless
all 10 digits from 0 to 9 occur in your string.

If you look in Excel help for the SEARCH function, it tells you:
"If find_text is not found, the #VALUE! error value is returned. "

 

[Jacob Skaria]

Sorry; forgot to add something..

So if the number to be extracted is of fixed length (say 5 as in your
example) you can use the MID function to extract the number..Hope you know
this already.

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),5)

If you dont know the length of the number you may use the below formula to
extract the first continuous numeric part of the text string..

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),COUNT(1*MID(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99),ROW($1:$9),1)))


If this post helps click Yes

 

[JLatham]

Another way of explaining that need is that by adding "0123456789" to the end
of the contents of A1 is that
#1 it guarantees some match (see David Biddulph's comment) and if it turns
out that the match is in that group, the position will be greater than the
length of the original string in A1 and so the test against LEN(A1) will
fail, telling the formula that there aren't any digits in the original string.

 

[macropod]

Hi Jacob,

Your formula will return a false match where the string has no number. Hence the extra testing in the version I posted.