Parsing an excel cell value using macro

Excellearner
Excellearner used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
An example workbook would be useful. Have you tried Text to Columns from the Data Tab?
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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

Author

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 you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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

Author

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.
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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

Author

Commented:
Subodh, thank you.

Still not removing the three dots when copying in column c.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial