N
Nikki
What formula could I use to pull 03275 from the below contents of a cell?
EA_1045_02949_03275 (John Doe)
EA_1045_02949_03275 (John Doe)
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Brad
one way
=right(a1,5)
This assume that your information is in a1
=right(a1,5)
This assume that your information is in a1
J
Jim Thomlinson
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...
=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...
R
Reeza
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.
J
Jim Thomlinson
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)))
=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)))
L
Lars-Åke Aspelin
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
R
Ron Rosenfeld
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
S
Shane Devenshire
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.
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.