Vlookup after LEFT, TRIM and still receiving ERROR

StickyNote1

New Member
Joined
Jul 19, 2019
Messages
10
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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.

GHIJ
4AS FAUSTINA V. 22AFAAFA
5Acapulco V. 22ACA#N/A
6AS FAUSTINA V. 22AFAAFA
7My formulaPost #6

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
I4=VLOOKUP(TRIM(LEFT(SUBSTITUTE(G4,CHAR(160)," "),SEARCH("V.",G4)-1)),'Vlookup Data'!B:C,2,0)
J4=VLOOKUP(SUBSTITUTE(LEFT(G4,SEARCH("/",SUBSTITUTE(G4,CHAR(160),"/",2))-1),CHAR(160)," "),'Vlookup Data'!B:C,2,FALSE)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Hello everyone i tried to apply the I4 formula to this problem but cant seem to get it. I have provided a Link for your ease of review to this issue:

https://www.dropbox.com/s/fxob5p0ofprl85f/ZZZZZZZZZZZZZ.xlsx?dl=0

The issue is I cant Vlookup Column C from Array B:C with Lookup Value Column E

Cheers.
 
Upvote 0
Hi, This is a different question from the previous one, there you were looking for the text left of "V." which had CHAR(160). Here you have everything same, except CHAR(160) as an invisible character in the lookup value and CHAR(32) or space in the table array. In this case below formula in Cell G4 and copying it down will work:

Code:
=VLOOKUP(SUBSTITUTE(E7,CHAR(160)," "),$B$7:$C$10,2,FALSE)
 
Upvote 0
Aryatect,

Your suggested formula worked great, thank you. I do have a question how you usually know when a cell has special characters to applie CHAR 160 or 62? Just by trial and error or do you have another process?
 
Upvote 0
Glad could help and thanks for the feed back. I usually look for each character like below:


Book1
BCDEFGHIJKLMNOPQRST
7CHARTERHIRE-LEASED
1412345678910111213141516171819
15CHARTERHIRE-LEASED
16677265828469821607273826945766965836968
Sheet1
Cell Formulas
RangeFormula
B15=MID($E$7,B14,1)
B16=CODE(B15)
 
Last edited:
Upvote 0
Hi, This is a different question from the previous one, there you were looking for the text left of "V." which had CHAR(160). Here you have everything same, except CHAR(160) as an invisible character in the lookup value and CHAR(32) or space in the table array. In this case below formula in Cell G4 and copying it down will work:

Code:
=VLOOKUP(SUBSTITUTE(E7,CHAR(160)," "),$B$7:$C$10,2,FALSE)

Aryatect,

Your suggested formula worked great, thank you. I do have a question how you usually know when a cell has special characters to applie CHAR 160 or 62? Just by trial and error or do you have another process?
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top