CODEWITHSUNDEEP
excelexcel-formula
Mark Pelletier
Mark Pelletier

Reputation: 1359

Selectively Removing Characters

I have an XLS dataset (33K records). This set contains substrings I would like to replace/remove.

Targets include

Dimensional Prefix, like: OD, ID, L, W, THK - The position of these prefix strings is not rigidly consistent in the data

Examples:

Before:
OD4.5MMXL38MM
L140MM
ID6MM
L14-18MMXW14-21MMXTHK24-26MM

After:
4.5MMX38MM
140MM
6MM
14-18MMX14-21MMX24-26MM

The substrings noted above appear within longer strings:

SCREW BONE OD2.7MMXL22MM SELF TAPPING LOCKING T8 STARDRIVE RECESS SS NON STERILE

Is there any way to use SUBSTITUTE() or REPLACE() to make these kinds of adjustments? Note: I can parse/isolate the dimension strings listed above - to narrow the focus - I just cannot remove those prefix strings effectively.

Thanks!

Upvotes: 0

Views: 68

Answers (1)

BrakNicku
BrakNicku

Reputation: 5991

With the dimension string in A1 you can clean the text with nested SUBSTITUTE formula: 

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"L",""),"OD",""),"ID‌​",""),"W",""),"THK","")

Upvotes: 1

Related Questions