Search code examples
stringexcelformulas

How do I export text from an excel cell with alphanumeric values into another column?

I have several cell entries in column B. They look similar to this:

1050670||Target Optical  4226||6132||7132
1051752||Wal-Mart Vision Ctr  305095||6132||7132
1052470||Wal-Mart Vision Ctr  301891||6132||7132
1054354||Naval Ambulatory Care Ctr||6132||7132

I need a formula that will extract only the text containing the name. Ideally it would look leave me with only:

Target Optical
Wal-Mart Vision Ctr
Wal-Mart Vision Ctr
Naval Ambulatory Care Ctr

Any help is GREATLY appreciated.

Solution

  • If you want to disregard the numbers within the Text pseudo-field, you will have to parse the split-out value closely for characters within ASCII 48-57.

          Text Only parsing

    That ugly formula in B1 is,

    =TRIM(LEFT(MID(A1, FIND("||", A1)+2, FIND("¶", SUBSTITUTE(A1, "||", "¶", 2))-FIND("||", A1)-2)&0, MIN(INDEX(ROW(INDIRECT("1:"&LEN(MID(A1, FIND("||", A1)+2, FIND("¶", SUBSTITUTE(A1, "||", "¶", 2))-FIND("||", A1)-2)&0)))+((CODE(MID((MID(A1, FIND("||", A1)+2, FIND("¶", SUBSTITUTE(A1, "||", "¶", 2))-FIND("||", A1)-2)&0),ROW(INDIRECT("1:"&LEN(MID(A1, FIND("||", A1)+2, FIND("¶", SUBSTITUTE(A1, "||", "¶", 2))-FIND("||", A1)-2)&0))),1))<48)+(CODE(MID(UPPER(MID(A1, FIND("||", A1)+2, FIND("¶", SUBSTITUTE(A1, "||", "¶", 2))-FIND("||", A1)-2)&0),ROW(INDIRECT("1:"&LEN(MID(A1, FIND("||", A1)+2, FIND("¶", SUBSTITUTE(A1, "||", "¶", 2))-FIND("||", A1)-2)&0))),1))>57))*1E+99,,))-1))

    Fill down as necessary. As bad as it looks, the calculation load on a medium array formula would dwarf it but the INDIRECT does make it volatile so get your stripped values and Copy, Paste Special Values back to remove the formulas.