Excel 2010 - how to add spaces to data within a cell based on variable character count 1

[ITALIAORIANA]

Hi,

I have data in one cell that is comprised of 3 different codes. I need to add a space between these codes that have 2 constant characters and one variable.

Example:
Code in cell - 123456ABCDH32568HA
I want - 123456 ABCD H32568HA

Code in cell - ABCDIHBG1234THLKDRWS
I want - ABCDIHBG 1234 THLKDRWS


I used =MID(A2,LEN(A2)-11,4)&" "&RIGHT(A2,8) to be able to get ABCD H32568HA and 1234 THLKDRWS but couldn't figure out how to get the rest of the code to work right.

The problem: The last code is always 8 characters, the middle code is always 4 characters, however the first code can be anywhere from 1 to 8 characters long.

I used =MID(A2,LEN(A2)-11,4)&" "&RIGHT(A2,8) to be able to get ABCD H32568HA

Thanks
Deana

 

[SkipVought]

Left(A2,Len(A2)-12)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mintjulep]

Is what you really want to do adding a space between groups of letters and groups of numbers? Regardless of length.

 

[ITALIAORIANA]

Hi mintjulep, yes, I need a space between groups of characters. The last group and middle group always have the same number of characters, it was the first group I was having issues with because that group character count varied.

Deana

 

[ITALIAORIANA]

Thanks Skip!! That worked exactly as needed.