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
ExcellearnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
An example workbook would be useful. Have you tried Text to Columns from the Data Tab?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

ExcellearnerAuthor Commented:
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
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

ExcellearnerAuthor Commented:
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.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try changing that line to the following line and see if that works...

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

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ExcellearnerAuthor Commented:
Subodh, thank you.

Still not removing the three dots when copying in column c.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
NorieAnalyst Assistant Commented:
Are you sure those dots are actually 3 separate characters?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.