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
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
Last Comment
Roy Cox
An example workbook would be useful. Have you tried Text to Columns from the Data Tab?
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
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
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
You're welcome!
For that, please replace line#9 with the following line...
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))
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.
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Subodh, thank you.
Still not removing the three dots when copying in column c.
Still not removing the three dots when copying in column c.
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.
Are you sure those dots are actually 3 separate characters?
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
