How do i get excel to not recognise / exclude characters from data dumped

[Macca69]

Hi Folks! Been a while since I last posted / asked for help! Hoping some of you learned folks out there may be able to help me.

The problem I'm having is in dumping data from our financial system and using VLookup on the data to recognise the string or text characters, and then populate a spreadsheet report with that data.

The financial system unfortunately from time to time changes the string of characters

So currently the data is searching for :

** Cash, salary and ...
** Administration
** Travel etc

However, sometimes, for unknown reasons, when you dump the report down, some cost centres show:
* Cash, salary and ...

OR
*** Cash, Salary and wages

So, my question is, how do I get excel and the lookup functions to basically ignore the number of * or spaces etc in front of the words from the data dump ... and just use the words as the key characters to search on??

Any help would be greatly appreciated!!
Many thanks
Macca

 

[mole999]

did you shorten your original VLOOKUP to get reid of lead spaces and * as they won't be there to be searched for now

 

[Macca69]

did you shorten your original VLOOKUP to get reid of lead spaces and * as they won't be there to be searched for now

Hi Mole999

Thanks for response, sorry I'd not had enough sleep and was completely not thinking straight and only later realised (stupid me!) that when using the VLookup, it only refers to the left hand column!! Duh ... as I had used your fix to remove the characters etc into the next column, I had to make the refernce the next column ... really simple and just completely kept missing it!! - often trying too hard to find a harder issue when the problem is right in front of you!!

Thanks again for your initial help and solution- file appears to be working fine - thus far! Fingers crossed!!