CODEWITHSUNDEEP
excelexcel-formula
user3213283
user3213283

Reputation: 65

MID & FIND Logical in Excel with a space

I have some data that looks like the following;

Paul Smith Motorcycles/ Inv#1250/ NQX

I am trying to retrieve the "Inv#1250" out of the data. I can do this by using the following query;

=MID(P154,FIND("INV",P154)+0,8)

This returns me the data.

However some lines have a space in between the INV like so...

Peter Pane/ Inv# 1264
Joe Bloe/ Inv# 1265
Mary Jane/ Inv# 1266

How do I write an expression/query that will search for INV#1250 OR INV# 1265 when there is sometimes a space inbetween the data I want to find?

Upvotes: 3

Views: 105

Answers (2)

barry houdini
barry houdini

Reputation: 46361

Another way would be to simply use SUBSTITUTE to remove spaces first, i.e.

=MID(SUBSTITUTE(P154," ",""),SEARCH("INV#",SUBSTITUTE(P154," ","")),8)

Upvotes: 0

Simon1979
Simon1979

Reputation: 2108

You could test if there is a space and then change the number of chars the MID returns:

=MID(B4,FIND("Inv",B4)+0,IF(ISERROR(FIND("Inv# ",B4)),8,9))

You could then extend with SUBSTITUTE to get consistency by removing the space:

=SUBSTITUTE(MID(B3,FIND("Inv",B3)+0,IF(ISERROR(FIND("Inv# ",B3)),8,9))," ","")

Upvotes: 3

Related Questions