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

How to derive multiple strings from a cell

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,

I have a text value stored in a cell. For example value in cell A1 is

“gibberish, gibberish gibberish, Yamaha: $1.00 Suzuki: $11.00 Honda: $111.00 KTM: $1,111.00

Bajaj: $11,111.00 Audi: $111,111.00 BMW: $11,111,11.00 Benz:

$11.00 gibberish gibberish gibberish gibberish , gibberish”

This value is imported from another format, it has lot of empty spaces between.

I am trying to get output as below

Column 1, Column 2, one by one

Yamha $1.00 (Cell address B1 and C1)
Suzuki $11.00 (Cell address B2 and C2)
Honda $111.00 (Cell address B3 and C3)
KTM $1,111.00 (Cell address B4 and C4)
Bajaj $11,111.00 (Cell address B5 and C5)
Audi $111,111.00 (Cell address B6 and C6)
BMW $1,111,111.00 (Cell address B7 and C7)
Benz $11.00 (Cell address B8 and C8)

Since the space between 2 keywords is not uniform, I am not able to derive values correctly.

Amount relating to Yamaha can 1 or 999,999, and I am sure how to identify this.


Words marked in bold are for reference purpose. I need to search these keywords in text.

I tried using mid function but not able to get values correctly, for Mid I need to know second parameter.

I am not able find this.


I will upload sample file in some time and notify the same. Can anyone please help me in this.
 
Hi @Debaser , thanks for asking, I missed it completely.

Real data would have keywords such as 'Yamaha Suzuki Honda' or 'Yamaha Honda' or 'Honda'.

Only thing is, these are fixed set of keywords that I need to find.
I will definitely mention this in sample file.

Thanks for the help. Good night. :)
 
Hi @Debaser , may I suggest one thing, since I know what keywords I need to find, I can replace all these keywords with a tempstring.

For example, we can replace 'Yamaha Honda Suzuki' with 'TempString'.
Will that make a bit easier ?

Once we trim the data and replace all the keywords with TempString I guess it will be bit easier to format.

But still I do not know how to get second part of Mid function.
 
Are the words actually in bold in your real file? Getting a single word and value is pretty simple, but you don't seem to have any usable delimiters to show which words to use.
 
Hi @Debaser , thanks a lot for the help. No, actual words are not in bold in real file.

Sir, I can replace all the keywords with tempstring and convert them into 1 word. For example, I will find 'Yamaha Suzuki Honda' and replace with one word as 'TempString'. I guess that should not be a problem for me.
 
Hi @Debaser , please refer attached file, might be helpful. This is what I am trying to do before proceesing. This will give me one word to process.
 

Attachments

  • Chandoo.xlsm
    9.7 KB · Views: 3
If you just replace the spaces in those key words, you can use this:

Code: 
Sub getItems()
    Dim oRE                   As Object
    Dim lCount                As Long
    Dim rngOut                As Range
    Dim sPattern              As String
    Dim sIn                   As String
    Dim matches
    Dim match

    Set oRE = CreateObject("vbscript.regexp")

    sIn = Range("A2").Value

    Set rngOut = Range("B4")

    sPattern = "\s[A-Z]+:\s+\$[\d,.]+"
    With oRE
        .IgnoreCase = True
        .Global = True
        .Pattern = sPattern
        Set matches = .Execute(sIn)
        lCount = matches.Count
        For Each match In matches
            match = Trim$(match)
            rngOut.Value2 = Trim$(Split(match, ":")(0))
            rngOut.Offset(, 1).Value2 = Trim$(Split(match, ":")(1))
            Set rngOut = rngOut.Offset(1)
        Next match
    End With
End Sub
 
Hi @Debaser sir, hope you are doing good. Above code work perfects with numbers.
I am facing problems with values as '12/10/15 11:10:11' and '($15,014,709,535.81)'.

I am not able to capture these values, could you please help if possible.

PS - Replaced "(" with blank, seems to be working, not sure though.
 
Hi,

Since, sequence of the words in imported text is going to be uniform.
I used below mentioned formula to get the results.

Range A1 value = 'Yamaha: $1 Honda: $11 Suzuki: $1,111 KTM: $11,111 Chandoo'

'=TRIM(MID($A$1,(LEN(B4)+(SEARCH(B4,$A$1))),((SEARCH(C4,$A$1))-(LEN(B4)+(SEARCH(B4,$A$1))))))'

Still in testing phase. Good night.
 

Attachments

  • Temp.xlsm
    9.6 KB · Views: 2
You must log in or register to reply here.
Back
Top