Formula help - letters to numbers & vice versa

[ferocious12]

Hi All,

I have to convert the product codes from letters to numbers and vice versa.

Existing code: 123F8
Conversion should be: ABC6H

Can you please help. I have attached a sample file. I have researched into "TextJoin" & "INDIRECT" but can't understand it fully.

Thanks!

 

[vletm]

ferocious12
... without those 'Original' & 'Converted'-values.

 

[herofox]

Very Nice

 

[ferocious12]

ferocious12
... without those 'Original' & 'Converted'-values.

Excellent ! many thanks

One variation if you can help. I have codes with varying length some 5 digits some upto 10. I can extend this formula to 10 digits but is there an error handling thing I can include so it pick up the number of digits in the code and adjust the formula accordingly?

 

[vletm]

ferocious12
If there are many variations ... I won't use formulas at all.
That formula already work only with that format.

 

[ferocious12]

ferocious12
If there are many variations ... I won't use formulas at all.
That formula already work only with that format.

Any code recommendation ?

 

[vletm]

ferocious12
You should able to show used variations with expected results
and
how would You use this?

 

[ferocious12]

ferocious12
You should able to show used variations with expected results
and
how would You use this?

Thanks. I have added few examples of the code. I will just run the macro to fill in the converted product code in column B

 

[bosco_yip]

If you have Office 365, try to use the TEXTJOIN function.

In C3, array formula (Ctrl+Shift+Enter) copied down :

=TEXTJOIN("",TRUE,IF(ISNUMBER(-MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)),IF(-MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1)=0,0,CHAR(64+MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1))),CODE(MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1))-64))

Regards
Bosco

 

[vletm]

ferocious12
As You have Excel - check this OC-function.

 

[ferocious12]

ferocious12
As You have Excel - check this OC-function.

Super ! you are a STAR !

Can you explain a bit about the code. Seems french from my knowledge of VBA

 

[vletm]

ferocious12
Which part is challenge for You?
This could explain eg like below with own grammar:
get 'original number' as A reset AB (which would be 'result') Check characters one-by-one if 'letter' then change to number as BB else change to letter as BB endif if 'letter' was zero then BB as 0 add BB in the end of AB Give 'result' as AB