Display ABC12345 as ABC 1234 5

[jonas-martens]

Hi,

Can't really find the correct format to display

ABC12345 as ABC 1234 5

So if I enter ABC12345, without any space character, Excel automatically displays ABC 1234 5.

Thanks.

[Sam Capricci]

if you want it done automatically and in the same cell you are probably going to need VBA. If you want it done by formula in another cell then this would work...
=LEFT(A1,3)&" "&MID(A1,4,4)&" "&RIGHT(A1,1)
it assumes all your data will be the same length

[jonas-martens]

Thanks Sabo kid.

Got a suggestion how to do this using VBA?

[Sam Capricci]

Sorry, I don't have the VBA skills. someone might come along with them to help though asking a moderator to move this post to the VBA area of the forum could get you the help faster.

[Glenn Kennedy]

Is the text ALWAYS ABC? Are there always 5 digits after it? Please provide a more representative selection of samples.

[jonas-martens]

Hi,

Letters at front are not always the same.
Always 6+1 numbers after it.
My example was just random.

Examples:
CLXU 451155 7
CLDU 969989 8
FMBU 003078 2
FMBU 003561 3
CLDU 962708 8
FMBU 008677 6

[AlKey]

Try this
Enter in B1 and copy down

=REPLACE( REPLACE(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1/17)),," "),LEN(A1)+1,," ")

v	A	B
1	CLXU4511557	CLXU 451155 7
2	CLDU9699898	CLDU 969989 8
3	FMBU0030782	FMBU 003078 2
4	FMBU0035613	FMBU 003561 3
5	CLDU9627088	CLDU 962708 8
6	FMBU0086776	FMBU 008677 6

[jonas-martens]

Wow thanks AlKey!

[CK76]

Just as reference, sample vba solution.

Sub Demo()
Dim cel As Range
Dim rng As Range
Set rng = Selection 'Change as needed
With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "([A-Z]*)(\d{6})(\d{1})"
    For Each cel In rng.Cells
        If .test(cel.Value) Then
            Set matches = .Execute(cel.Value)
            For i = 0 To 2
               res = IIf(Len(res) = 0, matches(0).Submatches(i), res & " " & matches(0).Submatches(i))
            Next
            cel.Value = res
            res = ""
        End If
    Next
End With
End Sub