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."
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
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
Joined
Jul 19, 2019
Messages
10
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
Joined
Jun 17, 2019
Messages
308
You can use dropbox or googledrive to upload your file and share the link here.
 

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
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
Joined
Jun 29, 2014
Messages
5,146
Hi,

Formula in Sheet 1 I4 copied down:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="background-color: #D0D0D0;;">AS FAUSTINA                   V.     22</td><td style="text-align: right;;"></td><td style=";">AFA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">AS FAUSTINA                   V.     22</td><td style="text-align: right;;"></td><td style=";">AFA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="background-color: #D0D0D0;;">AS FAUSTINA                   V.     22</td><td style="text-align: right;;"></td><td style=";">AFA</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1 </p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I4</th><td style="text-align:left">=VLOOKUP(<font color="Blue">TRIM(<font color="Red">LEFT(<font color="Green">SUBSTITUTE(<font color="Purple">G4,CHAR(<font color="Teal">160</font>)," "</font>),SEARCH(<font color="Purple">"V.",G4</font>)-1</font>)</font>),'Vlookup Data'!B:C,2,0</font>)</td></tr></tbody></table></td></tr></table><br />
 

StickyNote1

New Member
Joined
Jul 19, 2019
Messages
10
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
Joined
Jun 17, 2019
Messages
308
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
Joined
Jun 29, 2014
Messages
5,146
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.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="background-color: #D0D0D0;;">AS FAUSTINA                   V.     22</td><td style="text-align: right;;"></td><td style=";">AFA</td><td style=";">AFA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Acapulco                   V.     22</td><td style="text-align: right;;"></td><td style=";">ACA</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="background-color: #D0D0D0;;">AS FAUSTINA                   V.     22</td><td style="text-align: right;;"></td><td style=";">AFA</td><td style=";">AFA</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">My formula</td><td style=";">Post #6</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1 </p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I4</th><td style="text-align:left">=VLOOKUP(<font color="Blue">TRIM(<font color="Red">LEFT(<font color="Green">SUBSTITUTE(<font color="Purple">G4,CHAR(<font color="Teal">160</font>)," "</font>),SEARCH(<font color="Purple">"V.",G4</font>)-1</font>)</font>),'Vlookup Data'!B:C,2,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J4</th><td style="text-align:left">=VLOOKUP(<font color="Blue">SUBSTITUTE(<font color="Red">LEFT(<font color="Green">G4,SEARCH(<font color="Purple">"/",SUBSTITUTE(<font color="Teal">G4,CHAR(<font color="#FF00FF">160</font>),"/",2</font>)</font>)-1</font>),CHAR(<font color="Green">160</font>)," "</font>),'Vlookup Data'!B:C,2,FALSE</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,101,850
Messages
5,483,303
Members
407,393
Latest member
GeorgeBrown

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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top