CODEWITHSUNDEEP
excelexcel-formula
ian
ian

Reputation: 299

How to extract word before and after specified character?

Can anybody help me how to return word before and after specified character in excel this is word i have... the specified character is

#

14011242105#23140973#SAKTI AHMAD SYAHRONI# # # #BEKASI#10011994#L#T#KTP#3275050110940012# #10012015#667054753432000#HJ SITI SALAMAH#KP RAWA PANJANG. RT.01/04#KABUPATEN BEKASI# #KP RAWA PANJANG. RT.01/04#KABUPATEN BEKASI# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #01-APR-14# # #02871900858#152791162

thank you!

Upvotes: 0

Views: 1007

Answers (2)

RANGAN
RANGAN

Reputation: 26

You can use SUBSTITUTE function as under" If the text what you are quoting is in Cell A1 then in Cell A2 type the formula: = SUBSTITUTE(A1,"#"," ")
You will get in cell A2 the names and numbers without hash symbol

Upvotes: 0

Calvin
Calvin

Reputation: 443

The easiest way is to select the cells that contain your string then use Text to Columns:

Data tab > Data Tools group > Text to Columns

Select Delimited > Next

Check the box Other and type # in the box

Hit Next. You may want to change some of the columns to Text here, specifically those that contain numbers.

Hit Finish.

Upvotes: 3

Related Questions