Filename Problems

[Guest]

I currently have a excel macro that pulls information from the excel columns
and adds it to the filename. Then, the macro will convert to csv file. A
column exists in the file called EMPLOYER NAME. An individual will insert a
company's name in this column. This will be pulled into the filename.
However, if a company is ABC Company, Inc. - the comma in the filename throws
our printing system into a loop. So, I use the equation =LEFT(A2,FIND("
",A2))&MID(A2,FIND(" ",A2)+1,0) to only pull in the first portion of the name
into another column and then insert the new name into the filename. Works
good, but I am now running into the problem of the name being The ABC
Company, Inc. The comma is not present, but the filename is The. Does anyone
know an equation I could use that would only pick out ABC Company and leave
The and the comma out?

 

[Trevor Shuttleworth]

One way:

=LEFT(SUBSTITUTE(SUBSTITUTE(A2,"The ",""),",",""),FIND("
",SUBSTITUTE(SUBSTITUTE(A2,"The ",""),",",""),FIND("
",SUBSTITUTE(SUBSTITUTE(A2,"The ",""),",",""))+1)-1)

Doesn't look great and I'm sure there's a better way ... but built from
first principles

The SUBSTITUTE(SUBSTITUTE(... gets rid of the "The " and the comma. The
remainder is working out where the second space is, taking 1 off to give the
length and then uses LEFT to extract the data. Unfortunately, it will fail
if there is no second space. You would therefore need to extend the
equation to check for this condition.

You can count the spaces:

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))

So, you could test for the mini,um for the equation to work:

=IF((LEN(A2)-LEN(SUBSTITUTE(A2," ","")))>3,"the long equation","the shorter
version")

I'm struggling to work out the shorter version of the equation but hopefully
this will give you something to start with.

Regards

Trevor

 

[Guest]

=LEFT(A2,FIND(",",A2,1)-1)
will return all the text to the left of the comma

 

[Trevor Shuttleworth]

On the back of Gary's suggestion:

=LEFT(SUBSTITUTE(A2,"The ",""),FIND(",",SUBSTITUTE(A2,"The ",""))-1)

Again, no comma, no biscuit ... you'll get #VALUE! error, so you really need
to test for that.

It's relatively easy to produce a formula for a specific cell value but much
more so to allow for all the options.

Regards

Trevor

 

[Guest]

Thanks to all. It works like a charm.

One way:

=LEFT(SUBSTITUTE(SUBSTITUTE(A2,"The ",""),",",""),FIND("
",SUBSTITUTE(SUBSTITUTE(A2,"The ",""),",",""),FIND("
",SUBSTITUTE(SUBSTITUTE(A2,"The ",""),",",""))+1)-1)

Doesn't look great and I'm sure there's a better way ... but built from
first principles

The SUBSTITUTE(SUBSTITUTE(... gets rid of the "The " and the comma. The
remainder is working out where the second space is, taking 1 off to give the
length and then uses LEFT to extract the data. Unfortunately, it will fail
if there is no second space. You would therefore need to extend the
equation to check for this condition.

You can count the spaces:

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))

So, you could test for the mini,um for the equation to work:

=IF((LEN(A2)-LEN(SUBSTITUTE(A2," ","")))>3,"the long equation","the shorter
version")

I'm struggling to work out the shorter version of the equation but hopefully
this will give you something to start with.

Regards

Trevor

 

[Guest]

I have run into a problem, yes the word THE is taken care of and the commas
are fixed. However, the equation is not allowing employer names that do not
have The or commas. Any Suggestions?

 

[Trevor Shuttleworth]

Both my formulae and Gary's Student's formula will work regardless of
whether or not there is a "The " at the beginning. The difference is that
Gary's Student's formula leaves the "The " in the name and mine don't.

My first formula will work with or without the comma, the others don't
because they rely on finding the position of the comma ... which isn't
there. I did mention this in my response.

You could vary my version of Gary's Student's formula with:

=IF(ISERROR(FIND(",",A2)),A2,LEFT(SUBSTITUTE(A2,"The
",""),FIND(",",SUBSTITUTE(A2,"The ",""))-1))

This tests for the comma and, if it doesn't find one, uses the whole name.
If it does, it extracts the subset of characters.

Like I said, it's relatively easy to produce a formula for a specific cell
value but much
more so to allow for all the options.

What was the problem with the first formula? Or, alternatively, what was
the data that it failed on?

Regards

Trevor