We help IT Professionals succeed at work.

Parsing an excel cell value using macro

108 Views
Last Modified: 2019-04-11
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

Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
An example workbook would be useful. Have you tried Text to Columns from the Data Tab?
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED 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
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED 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
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Subodh, thank you.

Still not removing the three dots when copying in column c.
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED 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
CERTIFIED EXPERT

Commented:
Are you sure those dots are actually 3 separate characters?
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.