• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract Name from text string

badger

New Member
BILL PAYMENT VIA FASTER PAYMENT TO ABC Ltd REFERENCE UK , MANDATE 333
BILL PAYMENT VIA FASTER PAYMENT TO ACME LLC REFERENCE UK , MANDATE 375
TRANSFER TO A1 PUMPS Ltd REF: 1234

Dear All
Above is an extract from a report (3 entries) - but two different text strings. I need to remove the company name from the text.
Example using the first line would be:
A1 text above B1 ABC Ltd
A2 text above B2 ACME Ltd
A3 text above B3 A1 Pumps Ltd
Would anyone have an idea of a suitable formula to accommodate both sets of text fields.
Regards and thanks



 
Al alternative solution is to use Power Query. Here is the Mcode for that action

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Changed Type", {{"Column1", each Text.BetweenDelimiters(_, "TO", "REF"), type text}})
in
    #"Extracted Text Between Delimiters"

Data Range
A
1
Column1​
2
ABC Ltd​
3
ACME LLC​
4
A1 PUMPS Ltd​
 
@vletm
I noticed that the OP had indicated that he wanted Ltd even though the original data stated LLC. I assumed (maybe incorrectly) that the OP had a typo and actually wanted to extract the actual name and not change it. OP has two choices now--yours and mine. Let's see what he really wanted.

Alan
 
This version uses one of Charles Williams's FastExcel functions:
= Rgx.MID(@text, RegExpr)
where 'RegExpr' is set to (?<=TO )(\w+ )+(?=REF)

One or more words separated by a space: (\w+ )+
Followed by "REF" (positive lookahead): (?=REF)
Preceded by "TO" (positive lookbehind): (?<=TO )

If any regular expression users out there can improve the expression, I would be interested to try it; I am a RegEx novice.
 
Back
Top