Excel Query

[Clauric]

I have an Excel query regarding getting data from a cell.

Basically, I have multiple account names for an professional members organisation. Some of these are professional companies, and some are individual clients. However, there is no consistency in how the information has been entered.

For example, I might have:

IEIE Plc for John Smith
Potr SA A/C Michelle deSmith
Potr SA / Mr John Brown
IEIE Plc - John Reynolds
JDBN FBO NASFR

While the list above is small, it is a sample of about 30,000 different members, so manually cleaning it up is exceptionally time consuming, and prone to errors.

What I am looking to do is find some ways of splitting the cell into seperate cells. So for Potr SA A/C Michelle deSmith and Potr SA / Mr John Brown, I have the following command in a cell:

=IFERROR(IF(AND(MID(E26,FIND("/",E26)-1,1)="A",MID(E26,FIND("/",E26)+1,1)="C"),FIND("/",E26)-2,FIND("/",E26)),0)

Where E26 is the cell with the information in it. Using the return (9 for both), I cal use the Mid command to extract the information either side of the split. This will give the following information in the following cells:

Potr SA A/C Michelle deSmith
Potr SA Mr John Brown

This works well for any cell with "/" in it.

However, I am looking to get the same cell (where I have the command above) to also do a search for "-", "FBO", "for", etc. and return a value, so that I can use the MID function.

The FIND command can be used for "-", but I can't find a way of integrating the search into the above command (it keeps returning an error). FIND can't be used for "FBO", "for", etc as they will return more than one point.

Without using VBA, as the organisation will not allow macros (set at corporate level), is there any simple way to split/extract the information, and return a single value, without going through massive amount of cells.

Any help would be gratefully appreciated.

Regards.

 

[irakrakow]

Select the data. Then use the Data - Text to Columns (or press Alt, then A, then E), which will separate the data into columns. Check the Space separator, uncheck the Tab separator. Select a column where you want the text to be separated (say, column F so you preserve the original text). This will separate the text into columns.

Then, press Ctrl+T to create a table out of the data. You could also use the ribbon (Insert - Tables - Table). With a table, you can filter by each column, looking for hyphens, forward slashes, unnecessary spaces, and so on. As you filter, you can change Column E text as necessary. Hope this helps.

 

[knigget40]

If you want a formula vs. VBA Macro, here is an example. I am not sure how you wish to split each, but I assumed around the boundaries of each delimiter. One problem you may have is with the uniqueness of a delimiter, for example, you need to find “A/C” before you attempt to find “/”, and “ - “ before “-“. Anyway, here are two formulas, one finds the beginning of the delimiter, and the second finds the end of the delimiter. Not sure if this is what you wanted. Hopefully it helps.

Formula1 =IFERROR(FIND(" A/C ",A2), IFERROR(FIND(" / ",A2), IFERROR(FIND(" for ",A2), IFERROR(FIND(" FBO ",A2), IFERROR(FIND(" - ",A2), 0)))))

Formla2 =IFERROR(FIND(" A/C ",A2) + LEN(" A/C ")-1, IFERROR(FIND(" / ",A2) + LEN(" / ")-1, IFERROR(FIND(" for ",A2) + LEN(" for ")-1, IFERROR(FIND(" FBO ",A2) + LEN(" FBO ")-1, IFERROR(FIND(" - ",A2) + LEN(" - ")-1, 0)))))

To get first string, use result from first formula in D2, where A2 holds the original string =LEFT(A2,D2-1)

To get second string, use result from second formula in E2, where A2 holds the original string =RIGHT(A2,LEN(A2)-E2)