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]

what is your current vlookup ?

 

[Macca69]

the current lookup is telling it to look for the characters ** Cash, Salary and wages etc ...

What I need to do, is once the data is dumped down from the financial system into a raw data tab, simply remove all of the characters that appear before the words ... thus remove the ** (space space) or whatever may appear before the words.

It needs to be dynamic, to ignore the number of stars that may appear and also any spaces etc ...

My brain is not working - I'm sure there is an easy way to do this ...

Thanks

 

[Macca69]

[oops - didn't hit reply!!
the current lookup is telling it to look for the characters ** Cash, Salary and wages etc ...

What I need to do, is once the data is dumped down from the financial system into a raw data tab, simply remove all of the characters that appear before the words ... thus remove the ** (space space) or whatever may appear before the words.

It needs to be dynamic, to ignore the number of stars that may appear and also any spaces etc ...

My brain is not working - I'm sure there is an easy way to do this ...

Thanks

 

[mole999]

OK, so if you won't paste the VLOOKUP, look to build in TRIM and SUBSTITUTE

like =TRIM(SUBSTITUTE(C4,"*",""))

 

[Macca69]

OK, so if you won't paste the VLOOKUP, look to build in TRIM and SUBSTITUTE

like =TRIM(SUBSTITUTE(C4,"*",""))

Thanks Mole999 and apologies ... not sure on posting in the lookup and it is a very large complicated file with a series of tabs and formulae / lookups ... the key problem is the data coming direct from the financial system changing ... so i need to remove the things that change (ie the number of stars and / or spaces), before the text, once the data has been dumped down into a report tab .... I'll have a look and see what your solution says ... not sure if I can make it work just yet ... butr appreciate the response!!

i'll try some screen shots here - lookup below is from the raw data tab of the dump from the finance system - it is telling the system to look at the centre column and find that in the lookup table

Lookup table looks like this:

But as I said, the problem comes when the data in the finance download changes and is no longer ** Cash - Training, but may be * Cash - Training or *** - Cash Training etc ...

 

[Macca69]

Hi

OK, so if you won't paste the VLOOKUP, look to build in TRIM and SUBSTITUTE

like =TRIM(SUBSTITUTE(C4,"*",""))

Hi again

And using the formula you supplied, it does remove the * and spaces etc ... however, the VLookup on that column is now no longer working - I am assuming because it is focusing on the formual in the cell now, rather than the actual remain data? I've also tried:
in say cell B1 using formula to get the * characters removed
then in Cell C1 = cell B1
and then making the lookup refer to cell C1 ... but still coming up with no result ...

Any further suggestions anyone please??

 

[Macca69]

Hi anyone out there? - Still hoping for some help on this one??

My issue now seems to be how do I get a lookup forumla to not recognise the formula on the data, but rather the result?? So in the above, with the solution to my initial problem being to remove the * type characters, the solution was to use =TRIM(SUBSTITUTE(C4,"*",""))

This worked great and and it removed the * and spaces etc leaving only the words. My lookup formula is then pointing to that new cell ... which has the words formula in it ... rather than just the actual words / text??

I have tried reformatting the column to text only or general ... but no luck - it still gives me a result #n/a - help please!!

 

[Peter_SSs]

Would something like this be adaptable for you?

20 08 24.xlsm
	A	B	C	D	E	F
1
2	** Cash	1			** Cash	1
3	* Cash	1			** Administration	3
4	***** Cash	1
5	*** Administration	3
VLOOKUP

Cell Formulas
Range	Formula
B2:B5	B2	=VLOOKUP("~*~*"&SUBSTITUTE(A2,"*",""),E$2:F$3,2,0)

 

[Macca69]

Would something like this be adaptable for you?

20 08 24.xlsm
	A	B	C	D	E	F
1
2	** Cash	1			** Cash	1
3	* Cash	1			** Administration	3
4	***** Cash	1
5	*** Administration	3
VLOOKUP

Cell Formulas
Range	Formula
B2:B5	B2	=VLOOKUP("~*~*"&SUBSTITUTE(A2,"*",""),E$2:F$3,2,0)

Hi Peter - thanks so much for the reply!

I haven't as yet had a chance to look ast your solution, but will endeavour to do so in the morning and respond again!

Thanks again!!