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.
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.