Vlookup after LEFT, TRIM and still receiving ERROR

[StickyNote1]

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.

[TABLE="width: 287"]
<tbody>[TR]
[TD="class: xl67, width: 287"]AS FAUSTINA V. 22[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Aryatect]

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:

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

 

[StickyNote1]

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:

=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]

You can use dropbox or googledrive to upload your file and share the link here.

 

[StickyNote1]

https://www.dropbox.com/s/bwfeesj8cckfqmm/JT Test.xlsx?dl=0

Try this here is the link.

 

[Aryatect]

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:

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

 

[jtakw]

Hi,

Formula in Sheet 1 I4 copied down:

Book1
	G	H	I
4	ASFAUSTINAV.22		AFA
5	ASFAUSTINAV.22		AFA
6	ASFAUSTINAV.22		AFA
Sheet1

Cell Formulas
Range	Formula
I4	=VLOOKUP(TRIM(LEFT(SUBSTITUTE(G4,CHAR(160)," "),SEARCH("V.",G4)-1)),'Vlookup Data'!B:C,2,0)

 

[StickyNote1]

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]

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]

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.

Book1
	G	H	I	J
4	ASFAUSTINAV.22		AFA	AFA
5	AcapulcoV.22		ACA	#N/A
6	ASFAUSTINAV.22		AFA	AFA
7			My formula	Post #6
Sheet1

Cell Formulas
Range	Formula
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)