Introduction
LEFT, RIGHT, MID and FIND are useful Excel functions to help you extract specific parts of text or numbers from a cell.
The examples below show how to extract a server name from Fully Qualified Domain Domain, how to extract server names from LDAP attribute name, how to extract specific octets from an IP address, how to extract text from inside specific characters and how to trim specific characters from the start or end on a cell contents.
Use LEFT to extract server name from FQDN
If wanted add UPPER to enforce case
A | B | < B function > | |
---|---|---|---|
1 | servername | servername | =LEFT(A1,(FIND(“.”,A1&”.”)-1)) |
2 | servername | SERVERNAME | =UPPER(LEFT(A2,(FIND(“.”,A2&”.”)-1))) |
Use LEFT and SUBSTITUTE extract specific octets from IP address
A | B | < B function > | |
---|---|---|---|
1 | 192.168.1.1 | 192 | =LEFT(A1,FIND(“~”,SUBSTITUTE(A1,”.”,”~”,1))-1) |
2 | 192.168.1.1 | 192.168 | =LEFT(A2,FIND(“~”,SUBSTITUTE(A2,”.”,”~”,2))-1) |
3 | 192.168.1.1 | 192.168.1 | =LEFT(A3,FIND(“~”,SUBSTITUTE(A3,”.”,”~”,3))-1) |
4 | 0 | #VALUE | =LEFT(A4,FIND(“~”,SUBSTITUTE(A4,”.”,”~”,3))-1) |
If the source cell value is 0 Excel will return #VALUE
To allow for this use IF to return 0
A | B | < B function > | |
---|---|---|---|
1 | 192.168.1.1 | 192 | =IF(A1<>0,(LEFT(A1,FIND(“~”,SUBSTITUTE(A1,”.”,”~”,1))-1)),”0″) |
2 | 192.168.1.1 | 192.168 | =IF(A2<>0,(LEFT(A2,FIND(“~”,SUBSTITUTE(A2,”.”,”~”,2))-1)),”0″) |
3 | 192.168.1.1 | 192.168.1 | =IF(A3<>0,(LEFT(A3,FIND(“~”,SUBSTITUTE(A3,”.”,”~”,3))-1)),”0″) |
4 | 0 | 0 | =IF(A4<>0,(LEFT(A4,FIND(“~”,SUBSTITUTE(A4,”.”,”~”,3))-1)),”0″) |
Use MID and FIND to extract server name from LDAP attribute
MID is used to identify starting character of the server name – in this case the 4th
FIND is used to find the commas which is used to determine the end of the server name
A | B | < B function > | |
---|---|---|---|
1 | CN=servername,OU=Member Servers,DC=tecivo,DC=com | servername | =MID(A1,4,FIND(“,”,A1)-4) |
Use MID and FIND to extract text inside of brackets
A | B | < B function > | |
---|---|---|---|
1 | Integrated Lights-Out 3 (iLO 3) | iLO 3 | =MID(A1,FIND(“(“,A1)+1,FIND(“)”,A1)-FIND(“(“,A1)-1) |
Use LEFT and LEN to remove character(s) from end of text
e.g. to remove R from end of hostname of server located in cell A1
-1 determines how many characters to remove
A | B | < B function > | |
---|---|---|---|
1 | servernameR | servername | =LEFT(A1,LEN(A1)-1) |
Use RIGHT, LEFT and LEN Remove specific number of characters from a text entry – left side or right side
Remove a single character from the start of text – e.g. Q from Q2969339
-1 determines how many characters to remove
A | B | < B function > | |
---|---|---|---|
1 | Q2969339 | 2969339 | =RIGHT(A1,LEN(B=A1)-1) |
To do same but remove two characters from the right side of text – e.g. remove the Q and the R from 2969339QR
A | B | < B function > | |
---|---|---|---|
1 | 2969339QR | 2969339 | =LEFT(A1,LEN(A1)-2) |
Also see
Leave a Reply