Hi, I have the following mixed data in a cell that I want to separate into two. I am looking for a solution with the best degree of automation possible here. Cell content is "1,234, 56.78%".

Button/Macro or using "=". Anything that may help automate this as much as possible. I want to split the data into two adjacent columns.

Kindly help.
Who is Participating?
Katie PierceConnect With a Mentor Commented:
OK, after doing that, can you add a column with the formula =CONCATENATE(C2,D2)

where C2 is the 1 and D2 is the 234?

Then just format that cell to be a number with a comma separator?
wsom-itgAuthor Commented:
Result as:
Cell 1: 1,234
Cell 2: 56.78%

Note: The length of the number in Cell 1/2 may vary.
Katie PierceCommented:
Click on the Data Ribbon, click Text to Columns, select Delimited and Next, check the box next to Comma, then Next, finish Wizard as needed.
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

wsom-itgAuthor Commented:
Great solution! Unfortunately, it also separates the 1,234 into 1 and 234.
wsom-itgAuthor Commented:
Yeah that helps. Thank you!
aikimarkConnect With a Mentor Commented:
This should parse your content:
=LEFT(A1,FIND(", ",A1)-1)
=MID(A1,FIND(", ",A1)+2,999)

Open in new window

Martin LissConnect With a Mentor Older than dirtCommented:
Select the cell and run this macro. It assumes that there is a comma and a space following 1,234.

Sub SplitIt()
ActiveCell.Offset(0, 1) = Split(ActiveCell, ", ")(0)
ActiveCell.Offset(0, 2) = Split(ActiveCell, ", ")(1)

End Sub

Open in new window

Rob HensonFinance AnalystCommented:
With text to columns, you can use the space as the separator instead of comma.
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.