Data Separation Help

E

excelesha

Board Regular
Joined
Apr 19, 2023
Messages
65
Office Version
  1. 365
Platform
  1. Windows
So in one cell I have a bunch of items listed, and i want to separate them into individual cells (so separating them into 20 row items or so):
21-UP-1043, 21-UP-1041, 21-UP-1040, 21-UP-1044, 21-
UP-1057, 21-UP-1042, 21-UP-1056, 21-UP-1046, 21-UP-1045,
21-UP-1058, 21-UP-1048, 21-UP-1047, 27-UP-4502, 27-UP-
5502, 27-UP-4504, 27-UP-8003, 27-UP-5503, 27-UP-4503, 27-
UP-8022, 27-UP-8002, 27-UP-8021, 27-UP-4526, 27-UP-5524,
27-UP-4520, 27-UP-5518		21-UP-1043
21-UP-1041
etc….
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
excelesha said:
So in one cell I have a bunch of items listed, and i want to separate them into individual cells (so separating them into 20 row items or so):
21-UP-1043, 21-UP-1041, 21-UP-1040, 21-UP-1044, 21-
UP-1057, 21-UP-1042, 21-UP-1056, 21-UP-1046, 21-UP-1045,
21-UP-1058, 21-UP-1048, 21-UP-1047, 27-UP-4502, 27-UP-
5502, 27-UP-4504, 27-UP-8003, 27-UP-5503, 27-UP-4503, 27-
UP-8022, 27-UP-8002, 27-UP-8021, 27-UP-4526, 27-UP-5524,
27-UP-4520, 27-UP-5518		21-UP-1043
21-UP-1041
etc….
Click to expand...
the items are separated by comma. Thanks in advance.
 
Upvote 0
Is this what you mean?

Code: 
=TEXTSPLIT(B1,,", ")

Assuming the single cell is B1, then this formula could be in C1.
 
Upvote 1
Similar option
Fluff.xlsm
A
1
221-UP-1043, 21-UP-1041, 21-UP-1040, 21-UP-1044, 21-UP-1057, 21-UP-1042, 21-UP-1056, 21-UP-1046, 21-UP-1045,21-UP-1058, 21-UP-1048, 21-UP-1047, 27-UP-4502, 27-UP-5502, 27-UP-4504, 27-UP-8003, 27-UP-5503, 27-UP-4503, 27-UP-8022, 27-UP-8002, 27-P-8021, 27-UP-4526, 27-UP-5524,27-UP-4520, 27-UP-5518
321-UP-1043
421-UP-1041
521-UP-1040
621-UP-1044
721-UP-1057
821-UP-1042
921-UP-1056
1021-UP-1046
1121-UP-1045
1221-UP-1058
1321-UP-1048
1421-UP-1047
1527-UP-4502
1627-UP-5502
1727-UP-4504
1827-UP-8003
1927-UP-5503
2027-UP-4503
2127-UP-8022
2227-UP-8002
2327-P-8021
2427-UP-4526
2527-UP-5524
2627-UP-4520
2727-UP-5518
28
Sheet4
Cell Formulas
RangeFormula
A3:A27A3=TRIM(TEXTSPLIT(A2,,","))
Dynamic array formulas.
 
Upvote 1
Solution
kweaver said:
Is this what you mean?

Code: 
=TEXTSPLIT(B1,,", ")

Assuming the single cell is B1, then this formula could be in C1.
Click to expand...
this did split all the cells, except 2 of them for some reason. Thank you so much for helping, appreciate it.
 
Upvote 0
Fluff said:
Similar option
Fluff.xlsm
A
1
221-UP-1043, 21-UP-1041, 21-UP-1040, 21-UP-1044, 21-UP-1057, 21-UP-1042, 21-UP-1056, 21-UP-1046, 21-UP-1045,21-UP-1058, 21-UP-1048, 21-UP-1047, 27-UP-4502, 27-UP-5502, 27-UP-4504, 27-UP-8003, 27-UP-5503, 27-UP-4503, 27-UP-8022, 27-UP-8002, 27-P-8021, 27-UP-4526, 27-UP-5524,27-UP-4520, 27-UP-5518
321-UP-1043
421-UP-1041
521-UP-1040
621-UP-1044
721-UP-1057
821-UP-1042
921-UP-1056
1021-UP-1046
1121-UP-1045
1221-UP-1058
1321-UP-1048
1421-UP-1047
1527-UP-4502
1627-UP-5502
1727-UP-4504
1827-UP-8003
1927-UP-5503
2027-UP-4503
2127-UP-8022
2227-UP-8002
2327-P-8021
2427-UP-4526
2527-UP-5524
2627-UP-4520
2727-UP-5518
28
Sheet4
Cell Formulas
RangeFormula
A3:A27A3=TRIM(TEXTSPLIT(A2,,","))
Dynamic array formulas.
Click to expand...
this formula worked beautifully, it split all the data into correct rows/format. Thank you so much again @Fluff .
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Can someone help me figure out why this macro is skipping some cells (Conditional Formatting)?
Replies
9
Views
702
Coyotex3
C
C
  • Solved
Check if specific date has been used and if yes choose first next available date
Replies
8
Views
590
CaraEL
C
J
  • Question Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
1K
JEC
JEC
B
  • Question Question
average based on time
Replies
1
Views
385
baqer
B
C
  • Question Question
Need Help with VBA Code - Copy data below and above a specific text
Replies
1
Views
861
onlyadrafter
O

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top