Hi Chandoo,
Congratulations on the new Twins!
I need a formula or method to pull out some very specific text from a cell.
Here's an example:
[9/25: 12345678 1234ABCD This is a test to see if I can find a better way.]
I just want to pull out the 12345678 and the 1234ABCD into their own individual cells.
I came up with a complicated method using SEARCH and MID for the first string I need.
A1: [9/25: 12345678 1234ABCD This is a test to see if I can find a better way.]
1st formula
A2: =MID($A$1,SEARCH(" ",A1)+1,8)
A2: [12345678]
The question then becomes how do I convince Excel to skip to the next space and pull the next string.
I suppose this could be one solution.
A3: =MID($A$1,SEARCH(A2,A1)+9,8)
A3: [1234ABCD]
I really want to avoid using text to columns, delimited by spaces, so that I can preserve the rest of the text in A1, although I have to admit, once I pull out the numbers I need into A2 and A3, I don't need them any longer in A1. Just the text itself.
Now let me throw a curve ball into the mix to make a real challenge out of this.
In some instances, I also need to pull out a slightly longer string. Here's an example of what that might look like:
A1: [9/25: 12345678-90 1234ABCD This is a test to see if I can find a better way.]
In this case, I need 11 characters instead of 8. To help you here, there's another cell, say B1, which flags that 11 characters are needed. I know this could probably be a nested IF function.
-------------------------------------------------------------------------------|--------
{A} {B}
1 [9/25: 12345678-90 1234ABCD This is a test to see if I can find a better way.][bigger]
2 [12345678-90 ]
3 [1234ABCD ]
A2: =IF(B1="bigger",MID($A$1,SEARCH(" ",A1)+1,11),MID($A$1,SEARCH(" ",A1)+1,8))
A3: =IF(B1="bigger",MID($A$1,SEARCH(A2,A1)+12,8),MID($A$1,SEARCH(A2,A1)+9,8))
Is there a better way? =)
Thanks!!!
Eric~
Congratulations on the new Twins!
I need a formula or method to pull out some very specific text from a cell.
Here's an example:
[9/25: 12345678 1234ABCD This is a test to see if I can find a better way.]
I just want to pull out the 12345678 and the 1234ABCD into their own individual cells.
I came up with a complicated method using SEARCH and MID for the first string I need.
A1: [9/25: 12345678 1234ABCD This is a test to see if I can find a better way.]
1st formula
A2: =MID($A$1,SEARCH(" ",A1)+1,8)
A2: [12345678]
The question then becomes how do I convince Excel to skip to the next space and pull the next string.
I suppose this could be one solution.
A3: =MID($A$1,SEARCH(A2,A1)+9,8)
A3: [1234ABCD]
I really want to avoid using text to columns, delimited by spaces, so that I can preserve the rest of the text in A1, although I have to admit, once I pull out the numbers I need into A2 and A3, I don't need them any longer in A1. Just the text itself.
Now let me throw a curve ball into the mix to make a real challenge out of this.
In some instances, I also need to pull out a slightly longer string. Here's an example of what that might look like:
A1: [9/25: 12345678-90 1234ABCD This is a test to see if I can find a better way.]
In this case, I need 11 characters instead of 8. To help you here, there's another cell, say B1, which flags that 11 characters are needed. I know this could probably be a nested IF function.
-------------------------------------------------------------------------------|--------
{A} {B}
1 [9/25: 12345678-90 1234ABCD This is a test to see if I can find a better way.][bigger]
2 [12345678-90 ]
3 [1234ABCD ]
A2: =IF(B1="bigger",MID($A$1,SEARCH(" ",A1)+1,11),MID($A$1,SEARCH(" ",A1)+1,8))
A3: =IF(B1="bigger",MID($A$1,SEARCH(A2,A1)+12,8),MID($A$1,SEARCH(A2,A1)+9,8))
Is there a better way? =)
Thanks!!!
Eric~