Avatar of Excellearner
Excellearner
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Parsing an excel cell value using macro

Dear experts

I have an excel file with sever sheets.
In each sheet in column i have a listed text.

At the end of each text in every cell, i have something like this 'text...cited = 82'
The number after 'equal to varies from single to four digits.

So what I need is a macro which can pick a cell value under column B and put the text value (before '...cited = 82' in the adjacent cell (column C) and the number after 'equal to' in the adjacent cell under column D.

The number of rows can vary from as a few as 10 to 200 rows.

Kindly help.

Thank you
Microsoft OfficeMicrosoft ExcelVBA

Avatar of undefined
Last Comment
Norie

8/22/2022 - Mon
Roy Cox

An example workbook would be useful. Have you tried Text to Columns from the Data Tab?
Subodh Tiwari (Neeraj)

You may give this a try. If this doesn't produce the desired output, please upload a small sample workbook along with the desired output you are trying to achieve.
Sub TextToColumns()
Dim lr As Long
Dim Rng As Range, aCell As Range
Application.ScreenUpdating = True
lr = Cells(Rows.Count, 2).End(xlUp).Row
Set Rng = Range("B2:B" & lr)
For Each aCell In Rng
    If aCell.Value <> "" Then
        If InStr(LCase(aCell.Value), "cited") > 0 Then aCell.Offset(0, 1) = VBA.Trim(Split(aCell.Value, "cited")(0))
        If InStr(aCell.Value, "=") > 0 Then aCell.Offset(0, 2) = VBA.Trim(Split(aCell.Value, "=")(1))
    End If
Next aCell
Application.ScreenUpdating = True
End Sub

Open in new window

Excellearner

ASKER
Subodh,

It worked thank you.

But I request one small change in your code: the three dotes before 'cited ' should also be eliminated from the text to be pasted in column c.

kindly help
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Subodh Tiwari (Neeraj)

You're welcome!

For that, please replace line#9 with the following line...

If InStr(LCase(aCell.Value), "cited") > 0 Then aCell.Offset(0, 1) = VBA.Trim(Split(aCell.Value, "...cited")(0))

Open in new window

Excellearner

ASKER
Hi Subodh,

Now the copy is copying all the content text in column B and pasting it exactly in column C. It is not copying the text before '...cited...

In column D i am getting the number, i am fine with this.

Kindly help.
ASKER CERTIFIED SOLUTION
Subodh Tiwari (Neeraj)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Excellearner

ASKER
Subodh, thank you.

Still not removing the three dots when copying in column c.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Subodh Tiwari (Neeraj)

In that case please upload a sample workbook. I tested the code on some dummy data like "Hello World!...cited = 45" and it returned "Hello World!" in column C.
Norie

Are you sure those dots are actually 3 separate characters?