How can i remove the specific string with help of macro in Excel

Question

I'm analyzing sales report data. I need to remove some unnecessary string. This string will be generated from the POS. It's a default format.

This is the default format:

1-99550 Alexander Keith #4924) on 27/12/2018 1:16:09PM for 0.00 hrs 0.00 min 56.00 sec
1-99551 Mark Smith #4924) on 27/12/2018 1:16:09PM for 0.00 hrs 0.00 min 56.00 sec

I need to remove "#4924) on 27/12/2018 1:16:09PM for 0.00 hrs 0.00 min 56.00 sec " part of the string.

In case you're not sure about the number (it's not always going to be 4924), you might need "https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops". — Dominique, Jan 02 '19 at 15:52

score 2 · Answer 1 · answered Jan 02 '19 at 15:26

2

Just use LEFT()/SEARCH():

=LEFT(A1,SEARCH("#",A1)-1)

Returns

1-99550 Alexander Keith

answered Jan 02 '19 at 15:26

BruceWayne

1

Add a `Trim` to that if you don't want leading or trailing whitespaces – Zac Jan 02 '19 at 15:40

score 1 · Accepted Answer · answered Jan 02 '19 at 15:46

1

Just Find #* (and Replace with 'nothing').

answered Jan 02 '19 at 15:46

pnuts

2 Answers2