Excel

Översätt bokstäver till siffror

Translate Letters Numbers

Excel -formel: Översätt bokstäver till siffrorGenerisk formel | _+_ | Sammanfattning

För att översätta bokstäver i en sträng till siffror kan du använda en matrisformel baserad på funktionerna TEXTJOIN och VLOOKUP, med en definierad översättningstabell för att tillhandahålla nödvändiga sökningar. I exemplet som visas är formeln i C5:



{= TEXTJOIN ('',1, VLOOKUP ( T ( IF (1, MID (A1, ROW ( INDIRECT ('1:'& LEN (A1))),1))),xtable,2,0))}

där 'xtable' är namngivet intervall E5: F10.

Obs: detta är en matrisformel och måste anges med kontroll + skift + enter.





Förklaring

I grunden använder denna formel en array -operation för att generera en array bokstäver från inmatningstexten, översätter varje bokstav individuellt till ett tal, sammanfogar sedan alla siffror igen och returnerar utmatningen som en sträng.

För att analysera inmatningssträngen i en array eller bokstäver använder vi MID, ROW, LEN och INDIRECT -funktioner så här:



hur man använder naturlig logga in excel
 
{= TEXTJOIN ('',1, VLOOKUP ( T ( IF (1, MID (B5, ROW ( INDIRECT ('1:'& LEN (B5))),1))),xtable,2,0))}

LEN returnerar längden på den inmatade texten, som är sammanfälld till '1:' och överlämnas till INDIRECT som text. INDIRECT utvärderar texten som en radreferens och funktionen RAD returnerar en rad nummer till MID:

 
 MID (B5, ROW ( INDIRECT ('1:'& LEN (B5))),1)

MID extraherar sedan ett tecken för varje startposition och vi har:

 
 MID (B5,{123},1)

I huvudsak ber vi VLOOKUP att hitta en matchning för 'a', 'b' och 'c' samtidigt. Av oklara skäl , vi måste 'dereferera' denna array på ett visst sätt med både T- och IF -funktionerna. Efter VLOOKUP -körningar har vi:

 
= TEXTJOIN ('',1, VLOOKUP ( T ( IF (1,{'a''b''c'})),xtable,2,0))

och TEXTJOIN returnerar strängen '946'.

Mata ut ett nummer

För att mata ut ett tal som slutresultat (i stället för en sträng), lägg till noll. Matematikoperationen tvingar strängen till ett tal.

hur man låser celler i excel när man rullar

Summanummer

För att summera siffrorna tillsammans istället för att lista dem kan du ersätta TEXTJOIN med SUM så här:

 
= TEXTJOIN ('',1,{946})

Obs: TEXTJOIN -funktionen introducerades via Office 365 -prenumerationsprogrammet 2018.

Författare Dave Bruns


^