Split

[GoingPostal]

I Need to split a column in my excel file. I have attached a picture of the existing column. on some of the cells there is NA/GRN others have GRN/55 and others even have GRN/OR/55.
I need to seperate the /55 or similar and put it in a seperate column.
For Example:
GRN/OR/55 needs to seperate into GRN/OR in the original column and the /55 in the next column.

Any ideas?

 

[EFANYoutube]

Hi goingpostal
Have you tried the text to columns function in the data menu in the ribbon. You can set your own delimiter (in your case /) and it will remove the / and put all your different values in separate columns

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future, this might help: MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I need to seperate the /55 or similar and put it in a seperate column.

Is this what you mean?
If not, then more explanation and examples, including the expected results, please.

22 02 07.xlsm
	A	B
1	GRN
2	GRN/55	55
3	GRN/OR/55	55
4	OR/61	61
5	GRN/OR
6	OR/2	2
7	NA/GRN
Split

Cell Formulas
Range	Formula
B1:B7	B1	=IFERROR(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",20)),20)+0,"")

 

[GoingPostal]

Thank you for that Peter, i will try that plug in.

That is almost what i need but the forward slash needs to come over with the number and not be at the end of the column on the left

 

[GoingPostal]

Also i tried using that formula but it doesnt seem to work

 

[Peter_SSs]

That is almost what i need but the forward slash needs to come over with the number and not be at the end of the column on the left

Like this then?

22 02 07.xlsm
	A	B
1	GRN
2	GRN/55	/55
3	GRN/OR/55	/55
4	OR/61	/61
5	GRN/OR
6	OR/2	/2
7	NA/GRN
Split

Cell Formulas
Range	Formula
B1:B7	B1	=IFERROR("/"&RIGHT(SUBSTITUTE(A1,"/",REPT(" ",20)),20)+0,"")

 

[GoingPostal]

Thank you, that works. How if could i get it to set up like this?

GRN	GRN
GRN/55	GRN	/55
GRN/OR/55	GRN/OR	/55
OR/61	OR	/61

 

[Peter_SSs]

How if could i get it to set up like this?

Try this

22 02 07.xlsm
	A	B	C
1	GRN	GRN
2	GRN/55	GRN	/55
3	GRN/OR/55	GRN/OR	/55
4	OR/61	OR	/61
5	GRN/OR	GRN/OR
6	OR/2	OR	/2
7	NA/GRN	NA/GRN
Split

Cell Formulas
Range	Formula
B1:B7	B1	=LEFT(A1,LEN(A1)-LEN(C1))
C1:C7	C1	=IFERROR("/"&RIGHT(SUBSTITUTE(A1,"/",REPT(" ",20)),20)+0,"")

 

[GoingPostal]

That is perfect, thank you very much.

 

[Peter_SSs]

You're welcome. Glad to help.