Import comma separated data from a single cell

Ian Bell
Ian Bell used Ask the Experts™
on
I would like to display the comma separated numbers contained in a single cell into individual cells.
As under

Before
 A1
3.49,4.90,1,-3.49,9870.03
After
A1       A2        A3         A4          A5
3.49    4.90      1         -3.49       9870.03

Many Thanks
Ian
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Try this....

Sub SplitCommaSeparatedCellIntoMultipleCells()
Dim arr
arr = Split(Range("A1").Value, ",")
Range("A1").Resize(UBound(arr) + 1).Value = Application.Transpose(arr)
End Sub

Open in new window

Ian Bellretired

Author

Commented:
Hi Sktneer,
Thanks for this, however I am a novice when it comes to code. Can you tell me where to enter it and the steps to do so
Thanks
Ian
Microsoft Excel Expert
Top Expert 2014
Commented:
Please use Text to Columns feature in excel user the data tab click text to column then select delimited and then select comma and then next and finish
Ian Bellretired

Author

Commented:
Thanks, that worked a treat.

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