# Vlookup after LEFT, TRIM and still receiving ERROR

#### StickyNote1

##### New Member
Good day new to this community. I've been trying to use the following formula to have data on the LEFT of the same CELL to clean it and use it to VLOOK up another sheet and brining in a code.

Formula: =
VLOOKUP(LEFT(H4,(FIND("V.",TRIM(SUBSTITUTE(H4,CHAR(160),CHAR(32))))-2)),'Vlookup Data'!B:C,2,FALSE)

The Cell: contains the following( see below in BOLD and that's where im trying to get the AS FAUSTINA from and do Vlookup from another table.

 AS FAUSTINA V. 22

<tbody>
</tbody>

-------Tried to include small Sample excel sheet with but didn't see upload feature."

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### Aryatect

##### Active Member
Hi, your formula is working for me and giving AS FAUSTINA. Can you check if the value you are getting from the LEFT function is equal to AS FAUSTINA in Vlookup Data tab of the excel ?

Also try below:

Code:
``=VLOOKUP(LEFT(H4,SEARCH("/",SUBSTITUTE(H4," ","/",2))-1),'Vlookup Data'!B:C,2,FALSE)``

#### StickyNote1

##### New Member
Hi, your formula is working for me and giving AS FAUSTINA. Can you check if the value you are getting from the LEFT function is equal to AS FAUSTINA in Vlookup Data tab of the excel ?

Also try below:

Code:
``=VLOOKUP(LEFT(H4,SEARCH("/",SUBSTITUTE(H4," ","/",2))-1),'Vlookup Data'!B:C,2,FALSE)``
----------------------- @Aryatect Thank you for your feedback yes the formula works fine when its text. Thats what i wanted to include my sample sheet as the cell was pulled from a database and appears to have funky type of spacing. How can I share/upload my small sheet with the sample for you to look at?

#### Aryatect

##### Active Member
Hi, So this you have Char(160) in the first tab but second tab has CHAR(32) - "space" that is why it is not matching, below should work:

Code:
``=VLOOKUP(SUBSTITUTE(LEFT(G4,SEARCH("/",SUBSTITUTE(G4,CHAR(160),"/",2))-1),CHAR(160)," "),'Vlookup Data'!B:C,2,FALSE)``

#### jtakw

##### Well-known Member
Hi,

Formula in Sheet 1 I4 copied down:

#### StickyNote1

##### New Member
Thank you very much this worked like a charm. I now need to review your MAGIC formula to understand for future. But greatly appreciate your time and help. Cheers

#### Aryatect

##### Active Member
Glad we could help and thanks for the feedback.

Basically we are swapping the CHAR(160) with CHAR(32) which you did too, I was just searching for the second "space" and calculated from there and jtakw used TRIM after getting the data.

#### jtakw

##### Well-known Member
Not sure who's formula you're referring to, but just want to point out, formula posted in Post # 6 will fail for Single Word or more than 2 word Vessel names since it relies on the 2nd CHAR(160), whereas my formula in Post # 7 will work for Any number of words for Vessel names.

Anyhow, you're welcome, and welcome to the forum.

Last edited:

1,101,994
Messages
5,484,073
Members
407,427
Latest member
danbitton

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...