Solved

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%".

Posted on 2015-02-11
10
35 Views
Last Modified: 2015-03-12
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.
0
Comment
Question by:wsom-itg
  • 3
  • 2
  • 2
  • +2
10 Comments
 

Author Comment

by:wsom-itg
ID: 40603770
Result as:
Cell 1: 1,234
Cell 2: 56.78%

Note: The length of the number in Cell 1/2 may vary.
0
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40603827
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.
0
 

Author Comment

by:wsom-itg
ID: 40603837
Great solution! Unfortunately, it also separates the 1,234 into 1 and 234.
0
 
LVL 7

Accepted Solution

by:
Katie Pierce earned 168 total points
ID: 40603847
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?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:wsom-itg
ID: 40604014
Yeah that helps. Thank you!
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 166 total points
ID: 40604235
This should parse your content:
=LEFT(A1,FIND(", ",A1)-1)
=MID(A1,FIND(", ",A1)+2,999)

Open in new window

0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 166 total points
ID: 40604281
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

0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40605831
With text to columns, you can use the space as the separator instead of comma.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40660519
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now