Skill 1 Skill 2 Skill 3 Skill 4
PowerPoint, Excel, Power BI, Word #NAME? #NAME? #NAME? #NAME?
Word; Outlook, Excel #NAME? #NAME? #NAME?
Access: Excel #NAME? #NAME?
Excel; Power BI; SAP; Oracle #NAME? #NAME? #NAME? #NAME?
If there is only one type of delimitor in text then it Is easy to use TEXTSPLIT function, but
if our text in different cells contain different delimitors then we need to specify every
type of delimitor in formula within curli brackets. TEXTSPLIT(A2,{", ","; ",": "})
First Last
Mr. Tom Miller Tom Miller
Kim West Kim West
Lyric Wise Lyric Wise
Ms. Vanessa Chavez Vanessa Chavez
Reid Good Reid Good
Ms. Anna Brady Anna Brady
Skip Prefix and Suffix from any Text and also skip Blanks created due to removal of any content from Text
Step 1 Identify delimitor, in this case space is the delimitor
First Last TEXTSPLIT(A12," ")
Mr. Tom Miller #NAME? #NAME? #NAME?
Kim West #NAME? #NAME?
Lyric Wise #NAME? #NAME?
Ms. Vanessa Chavez #NAME? #NAME? #NAME?
Reid Good #NAME? #NAME?
Ms. Anna Brady #NAME? #NAME? #NAME?
Step 2
We need to remove Mr. and Ms. From prior to every name, for doing this add these prefix as delimitors in TEXTSPILT form
First Last
Mr. Tom Miller #NAME? #NAME? #NAME? #NAME?
Kim West #NAME? #NAME?
Lyric Wise #NAME? #NAME?
Ms. Vanessa Chavez #NAME? #NAME? #NAME? #NAME?
Reid Good #NAME? #NAME?
Ms. Anna Brady #NAME? #NAME? #NAME? #NAME?
Step 3
Next we have issue of blank cells, so wee need to remove blank cells in TEXTSPLIT formula
First Last
Mr. Tom Miller #NAME? #NAME?
Kim West #NAME? #NAME? TEXTSPLIT(A21,{" ","Mr.","Ms."},,TRUE)
Lyric Wise #NAME? #NAME?
Ms. Vanessa Chavez #NAME? #NAME?
Reid Good #NAME? #NAME?
Ms. Anna Brady #NAME? #NAME?
We skipped Row Delimitor and made active ignore empty option
mitors in TEXTSPILT formula
Leila=20000, Tom=50000, Kamil=60000, Anna=70000 Leila 20000
Tom 50000
Kamil 60000
Anna 70000
Next task is to split data from a single cell to multiple columns and Rows
In this 2 separate delimitors one as Column delimitor and one as Row delimitor will be used
Leila=20000, Tom=50000, Kamil=60000, Anna=70000 #NAME? #NAME?
#NAME? #NAME?
TEXTSPLIT(A9,"=",",") #NAME? #NAME?
#NAME? #NAME?
TEXTSPLIT, SORT AND REJOIN
WE Need to SORT the content of the cell which is seperated by delimitor
Skills Sorted TEXTJOIN(", ",TRUE,SORT(TRIM(
PowerPoint, Excel; Power BI:Google Sheets; Word Excel, Google Sheets, Power BI, PowerPoint, Word
Word, Outlook, Excel Excel, Outlook, Word
Access Access
Excel, Power BI, SAP, Oracle Excel, Oracle, Power BI, SAP
In first step split the text by TEXTSPLIT function and provide all type of delimitors and also specify remove blanks
PowerPoint, Excel; Power BI:Google Sheets; Word #NAME? #NAME?
Word, Outlook, Excel #NAME? #NAME?
Access #NAME?
Excel, Power BI, SAP, Oracle #NAME? #NAME?
Next we need to SORT content and also TRIM the output to remove any extra space
PowerPoint, Excel; Power BI:Google Sheets; Word #NAME? #NAME?
Word, Outlook, Excel #NAME? #NAME?
Access #NAME?
Excel, Power BI, SAP, Oracle #NAME? #NAME?
Next use TEXTJOIN Function to combine all the values again back into single cell
PowerPoint, Excel; Power BI:Google Sheets; Word #NAME?
Word, Outlook, Excel #NAME?
Access #NAME?
Excel, Power BI, SAP, Oracle #NAME?
OIN(", ",TRUE,SORT(TRIM(TEXTSPLIT(A4,{",",";",":"})),,,TRUE))
Point, Word
y remove blanks
#NAME? #NAME? #NAME?
#NAME?
#NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME?
#NAME? #NAME?
