Finding a position in a Cell using a formula

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

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&"012345678 9"))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.

--
Cheers
macropod
[Microsoft MVP - Word]


"jxbeeman" wrote in message ...
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

Take a look at this discussion:
http://www.microsoft.com/office/comm...f-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.

"jxbeeman" wrote:

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

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
---------------
Jacob Skaria


"jxbeeman" wrote:

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

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.



On 29 mei, 14:21, jxbeeman wrote:
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

What is the reason for the "0123456789" at the end -- A1&"0123456789"
Thanks,
Josh
"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"jxbeeman" wrote:

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

On Fri, 29 May 2009 05:21:01 -0700, jxbeeman
wrote:

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

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
---------------
Jacob Skaria


"jxbeeman" wrote:

What is the reason for the "0123456789" at the end -- A1&"0123456789"
Thanks,
Josh
"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"jxbeeman" wrote:

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

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. "
--
David Biddulph

"jxbeeman" wrote in message
...
What is the reason for the "0123456789" at the end -- A1&"0123456789"
Thanks,
Josh
"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"jxbeeman" wrote:

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

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&"01234 56789")),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&"01234 56789")),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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"jxbeeman" wrote:

What is the reason for the "0123456789" at the end -- A1&"0123456789"
Thanks,
Josh
"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"jxbeeman" wrote:

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