Search, count and replace text string

[rrocker1405]

Hi team,

I'm trying to do the following.

without delimiting the text string with comma as separators, I would like to count the length of characters in the below string and add 0 after AD if the number of characters is 6 and if 7 I would like to leave it as is. Having said this, the occurence of comma can be more than one time.

Current scenario: AD2314,AD23141
To Be scenario: AD02314,AD23141

can this be done?

Thanks in advance.

Kind regards,
A!

 

[bosco_yip]

Try,

=IF(FIND(",",A1)=7,REPLACE(A1,3,,0),A1)

 

[John Jairo V]

Hi @rrocker1405 (and @bosco_yip)

Having said this, the occurence of comma can be more than one time.

I think is better with VBA UDF:

Function FixNumbers$(R$)
    Dim a, i&
    a = Split(R, ",")
    For i = 0 To UBound(a)
        With Application
            a(i) = .Replace(a(i), 3, 0, .Rept(0, 7 - Len(a(i))))
        End With
    Next i
    FixNumbers = Join(a, ","): Erase a
End Function

And you can use the function:
=FixNumbers(A1)

Blessings!

 

[rrocker1405]

Thank you for the quick solution and reply John. Works like a charm.

Surely, it does help in fixing my solution. I was wondering if there is a way to do this without a VBA script just by using excel functions.

Kind regards,
A!

 

[John Jairo V]

Hi @rrocker1405

Check file. I did it with helper columns (you can hide it). You must extend the formula of D1 to the right depending on the number of codes you have in the cell (formula in C1 tell you how much columns do you need).

Likewise, the formula in B1 depends on the number of codes too (fix the numbers of cells to concatenate).

Blessings!

 

[rrocker1405]

Hi John,

Thank you once again...it works like a magic.

Wish you nice day ahead

Kind regards,
A!