Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filename Problems
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filename Problems
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 "Help?" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filename Problems
=LEFT(A2,FIND(",",A2,1)-1)
will return all the text to the left of the comma -- Gary's Student "Help?" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filename Problems
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 "Gary''s Student" wrote in message ... =LEFT(A2,FIND(",",A2,1)-1) will return all the text to the left of the comma -- Gary's Student "Help?" wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filename Problems
Thanks to all. It works like a charm.
"Trevor Shuttleworth" wrote: 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 "Help?" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filename Problems
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? "Help?" wrote: Thanks to all. It works like a charm. "Trevor Shuttleworth" wrote: 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 "Help?" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filename Problems
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,"T he ",""),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 "Help?" wrote in message ... 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? "Help?" wrote: Thanks to all. It works like a charm. "Trevor Shuttleworth" wrote: 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 "Help?" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell("filename") doesn't update to new filename when do save as. | Excel Worksheet Functions | |||
Converting a Variable Filename to a Constant Filename | Excel Programming | |||
set filename to <filename-date on open | Excel Worksheet Functions | |||
set excel <filename to <filename-date | Excel Programming | |||
Saving filename same as import filename | Excel Programming |