How To Use REPLACE, SEARCH, LEFT & CONCAT Functions To Transpose Values in Structured Referencing

Welcome to Excel Jet Consult blog post. In this tutorial, we want to learn how to user REPLACE, SEARCH, LEFT & CONCAT functions to transpose values located from middle to the left, right to the middle and left to the right (i.e. Larry, Gill Taylor to Gill Taylor Larry) . This operation can be performed easily by using the Flash Fill tool. However, we want to use formula to do this which will guarantee automatic update when the input values changes. Let’s get started.

Before we dive into this tutorial, we want to take a cursory look at all the functions we will work with and their syntax

REPLACE Function replaces part of a text string with a different text string

REPLACE Syntax                                =REPLACE(old_text, start_num, num_chars, new_text)

SEARCH Function returns the number of characters at which a specific character or text string is first found, reading left to right

SEARCH Syntax                                 =SEARCH(find_text, within_text, [start_num])

LEFT Function returns the given number of characters from the start of a text string

LEFT Syntax                                        =LEFT(text, [num_chars])

CONCAT Function concatenate a list or range of text strings

CONCAT Syntax                                =CONCAT(text1,…)

Below is the caption of the data we want to work with. Note, the dataset is stored in Excel Table with the name HR_Data

To transpose the values:

In cell D10, executive the formula: =REPLACE([@[Full Name]],1,SEARCH(“,”,[@[Full Name]]),””)&” “&LEFT([@[Full Name]],SEARCH(“,”,[@[Full Name]])-1)

Click Enter.

Operation performed. See the result below