Add spaces to long number string

[YLand]

Hello, the account number in the cell looks like: 300E23411130001000000. I need it to have spaces like: 300 E 23 4111 30 001 0 00 0 00. I've done it using LEFT, RIGHT AND MID before, but I can't remember how. I have a large report that I need to format this way. I am using Excel 16. Thank you in advance.

 

[Joe4]

Combine your LEFT, RIGHT, and MID functions with spaces.

So, if the value was in cell A1, the formula would start like this:

=LEFT(A1,3) & " " & MID(A1,4,1) & " " & MID(A1,5,2) & " " & ...

You should be able to pick up on the pattern and complete the rest.

 

[RobP]

Hi,

would this work for you, assuming data is in cell A1 ?

=LEFT(A1,3)&" "&MID(A1,4,1)&" "&MID(A1,5,2)&" "&MID(A1,7,4)&" "&MID(A1,11,2)&" "&MID(A1,13,3)&" "&MID(A1,16,1)&" "&MID(A1,17,2)&" "&MID(A1,19,1)&" "&MID(A1,20,2)

 

[YLand]

Combine your LEFT, RIGHT, and MID functions with spaces.

So, if the value was in cell A1, the formula would start like this:

=LEFT(A1,3) & " " & MID(A1,4,1) & " " & MID(A1,5,2) & " " & ...

You should be able to pick up on the pattern and complete the rest.

That's the one! Thank you so much!

 

[YLand]

Hi,

would this work for you, assuming data is in cell A1 ?

=LEFT(A1,3)&" "&MID(A1,4,1)&" "&MID(A1,5,2)&" "&MID(A1,7,4)&" "&MID(A1,11,2)&" "&MID(A1,13,3)&" "&MID(A1,16,1)&" "&MID(A1,17,2)&" "&MID(A1,19,1)&" "&MID(A1,20,2)

You just saved me an hour of googling! Thank you so much!

 

[Joe4]

You are welcome.
Glad we were able to help.

 

[RobP]

indeed, thanks a lot for the feedback.
cheers
Rob