Excel 2007 : Formula/formulas to add spaces

[DABlaylock]

Hi all,

I need some help, I work with numbers all day and I am looking for a way to split them up for easy reading. I have searched and written many failed formulas.

Numbers look like this
867010050T
N 10109210
1K0959455DT
561800375BN82V
561839432L VNH
561867180 92W
561711951

and I need to make them look like this
867 010 050 T
N 101 092 10
1K0 959 455 DT
561 800 375 BN 82V
561 839 432 L VNH
561 867 180 92W
561 711 951

I have been doing it manualy and I know there is a way to do it with formulas but I cant figure it out.

Thanks for any help,
David

[Paul]

Hi David, welcome to the forum.

What is the logic behind where the spacing occurs? If there is none, that's going to make it quite difficult to automate.

[DABlaylock]

These numbers are part numbers that we use in SAP. The way our SAP reads the part numbers is the first 11 digits are the main part number. If there is anything int places in 13, 14 and 15 is a color code. Also if the part number starts with a N there is 2 blank spaces after it (why who knowes, it is what it is). The reason we need to split them up is for easy reading for the material handlers.

The basic things I have to do is make sure if there is an N to put a space before it. Then every 3 positions a space utill position 11. If there is a color code in positions 12, 13 , and 14 they need to have a space before them.

I have a sheet I have made to take part numbers in any format and put them in our SAP format. I have attached it so you can look at it. It is still killing me that I can not figure out how to build one that adds spaces. Takeing them out is easy add turns out to be harder.:(

Thanks for any help or advise.SAP part number format.xlsm