Split Cells data into specific columns

Hello Experts,

Every week I get the column A, with the same or similar items, and every week I need to split "description", "gr" & "mm", so I would prefer anyone help me to build a formula for columns B, C & D, and then I would just have to paste my source data into column A, and the rest of columns would appear automatically.

Thanks in Advance

This proposed solution employs a helper column which may be hidden for aesthetic purposes.
The formula for the helper column (E) is:

Formula:

=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))

The formula for column B is:

Formula:

=LEFT(TRIM(A2),FIND(CHAR(7),SUBSTITUTE(TRIM(A2)," ",CHAR(7),E2-1)))

The formula for column C is:

Formula:

=MID(TRIM(A2),FIND(CHAR(7),SUBSTITUTE(TRIM(A2)," ",CHAR(7),E2-1)),FIND(CHAR(7),SUBSTITUTE(TRIM(A2)," ",CHAR(7),E2))-FIND(CHAR(7),SUBSTITUTE(TRIM(A2)," ",CHAR(7),E2-1)))

The formula for column D is:

Formula:

=RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND(CHAR(7),SUBSTITUTE(TRIM(A2)," ",CHAR(7),E2)))

Let us know if you have any questions.

Modify the formula in column C to read:

Formula:

=MID(TRIM(A2),FIND(CHAR(7),SUBSTITUTE(TRIM(A2)," ",CHAR(7),E2-1)),FIND(CHAR(7),SUBSTITUTE(TRIM(A2)," ",CHAR(7),E2))-2-FIND(CHAR(7),SUBSTITUTE(TRIM(A2)," ",CHAR(7),E2-1)))

Modify the formula in column D to read:

Formula:

=MID(TRIM(A2),FIND(CHAR(7),SUBSTITUTE(TRIM(A2)," ",CHAR(7),E2)),LEN(TRIM(A2))-1-FIND(CHAR(7),SUBSTITUTE(TRIM(A2)," ",CHAR(7),E2)))

Let us know if you have any questions.

Deleted by JeteMc

Originally Posted by farhangul

I have uploaded, just problem in number columns, I don't want gr or mm with numbers.

farhangul where is the upload? All I see in post#1 is a screenshot.

It is helpful to upload a sample Excel workbook.

If you are not familiar with how to do this:


To attach a file to your post,

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

Here are the formulas applied to the file that is now attached to post #1.
Note: the formulas in columns C and D have been wrapped in the VALUE function in the event that you would like to use the grams and/or mm's in further calculations. If you prefer the cells in columns C and D to display as text, remove the VALUE( function along with the last parenthesis.
Let us know if you have any questions.
Note: the formulas do not correctly parse the text in row 53 which is much different from that above or given in the screen shots.
Let us know if you have any questions.

Another way. It's a bit long. Enter in B2 fill across and down.

Formula:

=CHOOSE((COLUMNS($B:B)>1)+1,TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2," ",REPT(" ",LEN($A2)),LEN($A2)-

LEN(SUBSTITUTE($A2," ",""))-1),"mm",""),"gr ",REPT(" ",LEN($A2))),(COLUMNS($B:B)-1)*LEN($A2)+1,LEN($A2))),

--TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2," ",REPT(" ",LEN($A2)),LEN($A2)-LEN(SUBSTITUTE($A2," ",""))-1),

"mm",""),"gr ",REPT(" ",LEN($A2))),(COLUMNS($B:B)-1)*LEN($A2)+1,LEN($A2))))

B2=LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&1/17))-1)
C2=LOOKUP(10^10,RIGHT(LEFT($A2,SEARCH(C$1,$A2)-1),{1,2,3,4,5,6})+0)
D2=LOOKUP(10^10,RIGHT(LEFT($A2,SEARCH(D$1,$A2)-1),{1,2,3,4,5,6})+0)

Try this and copy towards down

or
b2=IFERROR(LEFT($A2,SEARCH(C2&C$1,A2)-1),LEFT($A2,SEARCH(D2&D$1,A2)-1))
try this and copy towards down

You're Welcome and Thank You for marking the thread as 'Solved'. I hope that you have a blessed day.