Breadcrumbs

Home Technical Help Excel string manipulation
Excel string manipulation PDF Print E-mail
Written by Administrator   

To manipulate strings in Excel Microsoft provide several functions, LEFT, RIGHT, LEN, FIND, TRIM, LOWER, UPPER to name a few.

Below example shows how to extract selected words from a string in a cell.

Example we are extracting the suburb name and the postcode from each row in column A

 

Column E shows the suburb extracted

=LEFT(RIGHT(A1,B1-9),D1-1) returns " Abbotsford" from "Suburb : Abbotsford (3067) 2.5 km "in A1

 

Column F shows the postcode

=LEFT(RIGHT(A1,B1-C1),4) returns "3067" from "Suburb : Abbotsford (3067) 2.5 km" in A1

 

Column C shows the number of spaces to find a particular character

=FIND("(",A1) returns 12 from "Suburb : Abbotsford (3067) 2.5 km" in A1


 

For more information on all Excel function click here