Formula Question

What formula could I use to pull 03275 from the below contents of a cell?

EA_1045_02949_03275 (John Doe)

one way
=right(a1,5)

This assume that your information is in a1
--
Wag more, bark less


"Nikki" wrote:

What formula could I use to pull 03275 from the below contents of a cell?

EA_1045_02949_03275 (John Doe)

Assuming the format and text lenght of the ID to always be the same then
=MID(A1, 15, 5)
If you need to seach for the text between the last _ and the first balnk
then we need to get a bit fancy...
--
HTH...

Jim Thomlinson


"Nikki" wrote:

What formula could I use to pull 03275 from the below contents of a cell?

EA_1045_02949_03275 (John Doe)

On May 14, 1:15*pm, Nikki wrote:
What formula could I use to pull 03275 from the below contents of a cell?

EA_1045_02949_03275 (John Doe)

If (John Doe) is part of the text.....

=MID(B10, LEN(B10)-FIND(" (", B10,1)+6, 5)

If the _XXXXXX is variable length you need a different approach
though.

Here is the fancy version...

=LEFT(MID(A1, FIND("^", SUBSTITUTE(A1, "_", "^",
LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))))+1, 256), FIND(" ", MID(A1, FIND("^",
SUBSTITUTE(A1, "_", "^", LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))))+1, 256)))
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Assuming the format and text lenght of the ID to always be the same then
=MID(A1, 15, 5)
If you need to seach for the text between the last _ and the first balnk
then we need to get a bit fancy...
--
HTH...

Jim Thomlinson


"Nikki" wrote:

What formula could I use to pull 03275 from the below contents of a cell?

EA_1045_02949_03275 (John Doe)

On Fri, 14 May 2010 13:15:01 -0700, Nikki
wrote:

What formula could I use to pull 03275 from the below contents of a cell?

EA_1045_02949_03275 (John Doe)

The following formula will pull characters 15 to 19 from the string in
A1:

=MID(A1,15,5)

Is that what you need?

The following formula will pull all characters between the rightmost
"_" and the next " ", assuming the result is within the 30 first
characters of the string:

=MID(A1,MAX(IF(MID(A1,ROW(1:30),1)="_",ROW(1:30))) +1,FIND(" ",
MID(A1,MAX(IF(MID(A1,ROW(1:30),1)="_",ROW(1:30)))+ 1,
MAX(IF(MID(A1,ROW(1:30),1)="_",ROW(1:30)))+1))-1)

Note this is an array formula that must be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Is that what you want?

As you see, you have to provide more information on the possible
format of the original string in order not to have us to guess what
you mean and to propose a formula that is as simple as possible, but
still working.

Hope this helps / Lars-Åke

On Fri, 14 May 2010 13:15:01 -0700, Nikki
wrote:

What formula could I use to pull 03275 from the below contents of a cell?

EA_1045_02949_03275 (John Doe)

To extract the value between the last underscore, and the following space:

=TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(
A1,"_",REPT(" ",99)),99))," ",REPT(" ",99)),99))

--ron

Don't need a formula:
1. Select the data
2. Choose Data, Text to Columns
3. Select Delimited, Next
4. Check Space and add _ to Other, then click Next
5. Highlight each column in the Data Preview pane that you don't want and
turn on the option Do not import (Skip) and click finish.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Nikki" wrote:

What formula could I use to pull 03275 from the below contents of a cell?

EA_1045_02949_03275 (John Doe)