Split Last word into separate column.

[ankit_9090]

Hello,

I have following data in 1 column and would like to split last word of each column in next column.

Example my data in 1 column looks like:

Moda_Dress_Style1_Black
Moda_Dress_Style1_White
Moda_Dress_Style4_Special_White
Modal_Dress_Style4_Special_Backorder_Pink

I would like to use formula or data text to comumn function to separate last word of each column , i.e color in next column.


Please help

[AliGW]

Welcome to the forum.

Try this in B1 copied down:

=TRIM(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",99)),98))

Excel 365 (Windows) 32 bit

	A	B
1	Moda_Dress_Style1_Black	Black
2	Moda_Dress_Style1_White	White
3	Moda_Dress_Style4_Special_White	White
4	Modal_Dress_Style4_Special_Backorder_Pink	Pink

Sheet: Sheet1

[ankit_9090]

Thank you Ali,

Also I would like to remove last words (color) from cell column A How do i do that ?

[AliGW]

You can't extract from existing data with a formula, but you could do this (e.g. in C1 copied down):

=SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(A1,"_",REPT(" ",99)),98*(LEN(A1)-LEN(SUBSTITUTE(A1,"_","")))))," ","_")

Excel 365 (Windows) 32 bit

	A	B	C
1	Moda_Dress_Style1_Black	Black	Moda_Dress_Style1
2	Moda_Dress_Style1_White	White	Moda_Dress_Style1
3	Moda_Dress_Style4_Special_White	White	Moda_Dress_Style4_Special
4	Modal_Dress_Style4_Special_Backorder_Pink	Pink	Modal_Dress_Style4_Special_Backorder

Sheet: Sheet1

[ankit_9090]

Thanks much! It worked !

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.