Excel Formatting

Question

How may I format a cell ( Excel ) to separate it into groups of 3 characters regardless of it Alpha Numeric content. For example ABC123 = ABC 123, 123ABC = 123 ABC, etc. Being done just to make reading easier. 

123ABC	123 ABC
ABC123	ABC 123

This becomes           ---------- This

Answer 1

Hi R626

I might be wrong but I'm afraid there is no native way to do it with custom cell formatting in excel

I see 3 options here

1-You may try using the formula given by RonRosenfeld above

2- Using a VBA macro

3- Using the FLASH FILL excel feature

Check this video (min: 3:35)

https://www.youtube.com/watch?v=5FSlXMzrHmQ

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 2

Hello Dave,

Thank you for getting back to me on this. I also thought that would work. But my problem her is that that doesn't work with the Alpha Numeric information that I am currently working with. The Alpha Numeric characters I am using are also in no logical sequence or order. They are specifically license numbers that I am just trying to make them easier to read by grouping smaller sequence of numbers if you will.

Answer 3

If you have the TEXTJOIN and SEQUENCE functions, try this formula:

=TEXTJOIN(" ",,MID(SUBSTITUTE(A1," ",""),SEQUENCE(LEN(A1)/3,,1,3),3))

123ABC	123 ABC
ABC123	ABC 123
ab cd 24 ef	abc d24 ef
xy ab cd ef gh	xya bcd efg h

Answer 4

Hi R626

I am Dave, an Independent Advisor, I will help you with this . . .

Select those cells, right click and choose Format Cells

Select Custom and in the 'Type' box, enter this manually: ### ###

Click OK . . .

.