Removing Text from a Cell

Cotta

Hi All,


I have a code in a cell to return a value and the value returned is exactly what I require, however I was wondering if it is possible to remove the last four characters returned and if so how is this done? The code I am using is as follows:

=VLOOKUP(A30,'Personal Finance'!A:IV,MATCH(My Details!H29,'Personal Finance'!1:1,0),0)

Stompa

https://support.office.com/en-gb/article/Remove-characters-from-text-49ec57f9-3d5a-44b2-82da-50dded6e4a68

Cotta

Thanks,
I have tried to use the solutions within this already, however the following returns errors:

=right(H30,-4),VLOOKUP(A30,'Personal Finance'!A:IV,MATCH(My Details!H29,'Personal Finance'!1:1,0),0)

Carrot007

=right(VLOOKUP(A30,'Personal Finance'!A:IV,MATCH(My Details!H29,'Personal Finance'!1:1,0),0),len(VLOOKUP(A30,'Personal Finance'!A:IV,MATCH(My Details!H29,'Personal Finance'!1:1,0),0)-4)

but did you not ask for =left in the first post?

bingo_bango

Cotta wrote: »

Thanks,
I have tried to use the solutions within this already, however the following returns errors:

=right(H30,-4),VLOOKUP(A30,'Personal Finance'!A:IV,MATCH(My Details!H29,'Personal Finance'!1:1,0),0)

This is just a syntax error.
This version is trying to remove the last 4 characters from the cell, and then doing the lookup. It can't remove characters if the cell is blank (which it will be until the lookup completes).

Cotta

Carrot007 wrote: »

=right(VLOOKUP(A30,'Personal Finance'!A:IV,MATCH(My Details!H29,'Personal Finance'!1:1,0),0),len(VLOOKUP(A30,'Personal Finance'!A:IV,MATCH(My Details!H29,'Personal Finance'!1:1,0),0)-4)

but did you not ask for =left in the first post?

Yes it's the last four characters in the cell that I am trying to remove. Attempting your formula (copied below) and I am being returned the "#VALUE!" error message.


=RIGHT(VLOOKUP(A30,'Personal Finance'!A:IV,MATCH(My Details!$H$29,'Personal Finance'!$1:$1,0),0),LEN(VLOOKUP(A30,'Personal Finance'!A:IV,MATCH(My Details!$H$29,'Personal Finance'!$1:$1,0),0)-4))

rmg1

Cotta wrote: »

Yes it's the last four characters in the cell that I am trying to remove. Attempting your formula (copied below) and I am being returned the "#VALUE!" error message.


=RIGHT(VLOOKUP(A30,'Personal Finance'!A:IV,MATCH(My Details!$H$29,'Personal Finance'!$1:$1,0),0),LEN(VLOOKUP(A30,'Personal Finance'!A:IV,MATCH(My Details!$H$29,'Personal Finance'!$1:$1,0),0)-4))

Looks like an issue with brackets. Try this:-
=RIGHT(VLOOKUP(A30,'Personal Finance'!A:IV,MATCH(My Details!$H$29,'Personal Finance'!$1:$1,0),0),LEN(VLOOKUP(A30,'Personal Finance'!A:IV,MATCH(My Details!$H$29,'Personal Finance'!$1:$1,0),0))-4)

bod1467

This whole thing would be greatly simplified it you simply put the VLOOKUP formula in a cell/column, set that column to Hide, then used an adjacent cell/column for the LEFT or RIGHT formula.

Will =RIGHT(Cell,-4) even work? I've never seen the LEFT or RIGHT commands used with negative numbers. The obvious formula would be:

=LEFT(Cell,LEN(Cell)-4)

where Cell is the (hidden) cell containing the VLOOKUP.

Cotta

Thanks very much folks, error has now been corrected.