• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search for non-specific Text or numbers in a text cell.

ericlind

New Member
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~
 

keymaster

New Member
hmm. you approach is more or less perfect. Splitting text is a pain with excel formulas. You can try maintaining a few helper columns to keep track of each space in the text and then use these numbers to shorten your formulas. here is an example http://chandoo.org/wp/2008/09/08/split-text-excel-functions/
 
Top