How to extract exact words to a different cell, errors in formula

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
116
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
So I have two text strings, in seperate lengths, I want my formula to be able to pick up the CH12 or GB01 and copy it to a different cell.
I am tring to get it to go through a Vlookup as there are a lot of company codes. Ideally what I would like it to do is to look at the text string finds the CH12, and outputs just the CH12 to another cell.

Column B
IC-Beleg 2272209 buchen / BUK= CH12 LF=df-mp
Complete IC-document 2266090 / BUK= GB01 LF=company

This is the closest I got, but it sometimes reports back the wrong results

=INDEX(Maintance!$A$1:$A$12,SUM(COUNTIF($B2801,"*"&Maintance!$A$1:$A$12&"*")*ROW(Maintance!$A$1:$A$12)))

<colgroup><col></colgroup><tbody>
</tbody>

lookup table

Company Codes
CH
BE
CZ
DE
DK
ES08
FR07
GB
NL03
NL07
US06

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>


Test Results

Complete IC-document 2272507 / BUK= FR07 LF=Rpc Superfos La GeneteFR07
Complete IC-document 2272198 / BUK= ES08 LF=Extra Transp.Internacionais LdaES08
Complete IC-document 2272198 / BUK= ES08 LF=Extra Transp.Internacionais LdaES08
IC-Beleg 2272209 buchen / BUK= CH12 LF=df-mpDE
Complete IC-document 2266090 / BUK= GB01 LF=companyGB
Complete IC-document 2272508 / BUK= FR07 LF=Rpc Superfos La GeneteFR07
Complete IC-document 2272475 / BUK= ES08 LF=Reclamos Vigo, S.L.ES08
Complete IC-document 2271331 / BUK= US06 LF=Iron Mountain/Safesite Inc.US06
Complete IC-document 2271336 / BUK= US06 LF=US06
Complete IC-document 2271347 / BUK= US06 LF=Parker, Holly PC CustodianUS06

<colgroup><col><col></colgroup><tbody>
</tbody>

thanks for any help.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
I could get away with the vlookup, It just needs to extract the text.
 
Upvote 0
=MID(B53,SEARCH("=",B53)+1,SEARCH("=",B53,SEARCH("=",B53)+1)-SEARCH("=",B53)-3)

Ive done it
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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