Excel value to specific Ascii

**siprian123** · 11-13-2022, 09:46 AM

Hello,

I'm trying to find to correct formula to get the text from a2 cell to be transformed in ASCII in B2 cell.
At this time, I'm using an website to get the correct ASCII

Example

Word - test
ASCII - 74,00,65,00,73,00,74,00,

Word - testing123
ASCII - 74,00,65,00,73,00,74,00,69,00,6E,00,67,00,31,00,32,00,33,00,

Can anyone help with the correct formula.
Thanks,
John

**josephteh** · 11-13-2022, 10:54 AM

Try, =CONCAT(IFERROR(DEC2HEX(CODE(MID(A1,{1,2,3,4,5,6,7,8,9,10},1)))&",00,","")), ctrl+shift+enter if necessary.

**siprian123** · 11-13-2022, 11:46 AM

got only 74,00

**FlameRetired** · 11-13-2022, 02:10 PM

siprian123 is your profile up to date? If not please do so. It helps us help you. If you have a newer version than 2010 there are new functions that might do the job. For example the CONCAT function that josephteh proposed is one of them.

**siprian123** · 11-13-2022, 02:26 PM

Hi Dave,

I forgot to update my profile after I purchased a new laptop . Now I have office 2019. Sorry for the inconvenience.

Thank you for taking the time to look at my post

**FlameRetired** · 11-13-2022, 02:47 PM

Thank you for updating.

You may have to array enter this as josephteh indicates. Drawing on josephteh's idea try

Formula:

Please Login or Register  to view this content.

**FlameRetired** · 11-13-2022, 02:54 PM

Although this is a little shorter and much simpler.

Formula:

Please Login or Register  to view this content.

**siprian123** · 11-13-2022, 07:58 PM

I used array for both formulas and the result is a cell with no value

Final value is

{=CONCAT(IFERROR(CHAR(HEX2DEC(MID(A1,(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*3+1,2))),""))}

{=CONCAT(IFERROR(CHAR(HEX2DEC(MID(A1,(ROW(INDIRECT("1:"&(LEN(A1)+1)/3))-1)*3+1,2))),""))}

I use Microsoft Office Prof Plus 2019 and the ASCII cell is formatted General