# Microsoft Excel formula question

#

**1**DVD Talk Limited Edition

Thread Starter

**Microsoft Excel formula question**

You guys have been great in the past about excel questions so I'm hoping I can do what I want to do. I just can't figure out how.

I have a column of information that is separated by commas. Within that information I want copy some of it to designated cells. I've looked through the text formulas and just can't figure out how to get it to move the information I need.

Basically the string in a single cell looks like this

This is what each of those mean for the curious

SS - Material Type

304 - Also material type, but the actual value I need

16ga - Material gauge

48x96 - Material width x Material Length

Now in 4 cells to the right of the cell with the string I have it setup like this

I'm trying to have each of the 4 cells I added look through that string and pull out only the information I want. For instance:

304

16

48

96

So am I out of luck or is this an easy fix? I'm thinking I can give my cells a string of text to look for in that column and output the result based on if it finds any of the information I designate it to look for.

For example Column B may have the strings of information (SS,304,16GA,48X96,) . Column C may be material. Can I designate values I want Column C to look for within Column B and out put based on if it finds any? We have about 10-15 different kinds of material so can I give the cell all of the materials separated by commas to look for and if it finds it return that exact result.

I have a column of information that is separated by commas. Within that information I want copy some of it to designated cells. I've looked through the text formulas and just can't figure out how to get it to move the information I need.

Basically the string in a single cell looks like this

SS,304,16GA,48X96,SS,304,16GA,48X96,

This is what each of those mean for the curious

SS - Material Type

304 - Also material type, but the actual value I need

16ga - Material gauge

48x96 - Material width x Material Length

Now in 4 cells to the right of the cell with the string I have it setup like this

**Material | Gauge | Width | Length**I'm trying to have each of the 4 cells I added look through that string and pull out only the information I want. For instance:

__Material__304

__Gauge__16

WidthWidth

48

__Length__96

So am I out of luck or is this an easy fix? I'm thinking I can give my cells a string of text to look for in that column and output the result based on if it finds any of the information I designate it to look for.

For example Column B may have the strings of information (SS,304,16GA,48X96,) . Column C may be material. Can I designate values I want Column C to look for within Column B and out put based on if it finds any? We have about 10-15 different kinds of material so can I give the cell all of the materials separated by commas to look for and if it finds it return that exact result.

*Last edited by kantonburg; 01-18-08 at 01:36 PM.*

#

**3**Stealth Moderator

Originally Posted by

**jonnyquest**Is the number of digits in each field always the same?

2digits,3digits,2digits(GA),2digits(X)2digits ??

2digits,3digits,2digits(GA),2digits(X)2digits ??

#

**4**Stealth Moderator

I'm sure there's a MUCH BETTER way than this, but see if this will work:

http://www.coldpumpkin.com/upload/kantonburg.xls

As long as the commas are all there and there is an "X" in the width and length, this should work.

http://www.coldpumpkin.com/upload/kantonburg.xls

As long as the commas are all there and there is an "X" in the width and length, this should work.

#

**5**DVD Talk Limited Edition

Thread Starter

Sorry for the late response. I left work early.

Yeah some of the data has a different number of digits. I was looking through the left,mid,right functions.

For instance we have in stainless

304 & 304L.

Most are 3 digits though. The information is derived from CrystalReports. I'm not sure how familiar you guys are with this. I'm working with our Controller to hopefully come up with a CrystalReport that can auto filter the information I need.

Yeah some of the data has a different number of digits. I was looking through the left,mid,right functions.

For instance we have in stainless

304 & 304L.

Most are 3 digits though. The information is derived from CrystalReports. I'm not sure how familiar you guys are with this. I'm working with our Controller to hopefully come up with a CrystalReport that can auto filter the information I need.

#

**6**DVD Talk Limited Edition

Thread Starter

Originally Posted by

**namja**I'm sure there's a MUCH BETTER way than this, but see if this will work:

http://www.coldpumpkin.com/upload/kantonburg.xls

As long as the commas are all there and there is an "X" in the width and length, this should work.

http://www.coldpumpkin.com/upload/kantonburg.xls

As long as the commas are all there and there is an "X" in the width and length, this should work.

You are awesome. Commas are all there and afaik the X is between all the lengths and widths. I almost can't wait to get to work monday in implement that in the spreadsheet.

thanks man. I'll let you know as soon as I get the sheet setup!

Now all I need to do is figure out how it works

#

**8**DVD Talk Limited Edition

Thread Starter

Originally Posted by

**dleedlee**The output looks like a basic comma delimited file. Couldn't you just rename the output as myfile.csv and import into Excel as a comma delimited file?

#

**11**Stealth Moderator

Like I said, there's probably a much better way.

Yeah, once you export as CSV and import as comma delimited, you'll still need to parse the data with left/mid/right, but it'll be a lot easier than the madness that I created.

Yeah, once you export as CSV and import as comma delimited, you'll still need to parse the data with left/mid/right, but it'll be a lot easier than the madness that I created.

#

**13**DVD Talk Limited Edition

Thread Starter

Originally Posted by

**namja**So ... did you try it? Did it work? Or did you not go into work today b/c it's holiday?

I tried outputting in different formats, but the description column always stays put with the commas. I'm going to try to alter the Crystal Report to see if I can separate the report output today.

#

**14**DVD Talk Hall of Fame

Join Date: Jul 1999

Location: Columbia, MD

Posts: 8,173

Likes: 0

Received 0 Likes
on
0 Posts

Try this:

1. Save the file as a tab delimited file in excel

2. Open the file with any text editor

3. Search and replace with nothing any double quotes (") and save

4. Rename the file with a .csv extension

5. Open with Excel. The data should be separated into columns, and you can just cut and paste the columns you need.

1. Save the file as a tab delimited file in excel

2. Open the file with any text editor

3. Search and replace with nothing any double quotes (") and save

4. Rename the file with a .csv extension

5. Open with Excel. The data should be separated into columns, and you can just cut and paste the columns you need.

#

**15**DVD Talk Limited Edition

Thread Starter

Originally Posted by

**Duran**Try this:

1. Save the file as a tab delimited file in excel

2. Open the file with any text editor

3. Search and replace with nothing any double quotes (") and save

4. Rename the file with a .csv extension

5. Open with Excel. The data should be separated into columns, and you can just cut and paste the columns you need.

1. Save the file as a tab delimited file in excel

2. Open the file with any text editor

3. Search and replace with nothing any double quotes (") and save

4. Rename the file with a .csv extension

5. Open with Excel. The data should be separated into columns, and you can just cut and paste the columns you need.

Well I tried The information, over the years, just hasn't been entered in the same order. So sometime gauge will be before length and sometimes afterwards.

I do however have the list formatted where the material and gauge will show up in the columns using the formula namja gave me, but the length and width aren't. I'm still working with it though.

#

**16**DVD Talk Legend

Originally Posted by

**kantonburg**Well I tried The information, over the years, just hasn't been entered in the same order. So sometime gauge will be before length and sometimes afterwards.

If so, import it into Excel. You would now have data in four columns (A, B, C, D). Then go to E1 and enter "=IF(ISERROR(FIND("X",C1)),D1,C1)" (minus the quotes). In F1, enter "=IF(ISERROR(FIND("X",C1)),C1,D1)" (minus the quotes). Fill the whole E and F column. E will now contain the dimensions and F will contain the gauge.

Note: I assume there is no X in a value in the gauge column. If there is, you'll have to modify this slightly, but the idea behind it will still work.

#

**17**DVD Talk Hall of Fame

Join Date: Jul 1999

Location: Columbia, MD

Posts: 8,173

Likes: 0

Received 0 Likes
on
0 Posts

I was going to post something similar to Jeremy517. Once you've got everything into columns, applying a formula to pull out specific data would be much easier. It lets you skip the parsing step.

#

**18**Moderator

Originally Posted by

**dtcarson**text to columns, using 'comma' as the delimiting character?

#

**19**DVD Talk Limited Edition

Thread Starter

Originally Posted by

**Jeremy517**Am I correct that sometimes you are seeing, for example, "SS,304,16GA,48X96" and sometimes you see "SS,304,48X96,16GA"?

If so, import it into Excel. You would now have data in four columns (A, B, C, D). Then go to E1 and enter "=IF(ISERROR(FIND("X",C1)),D1,C1)" (minus the quotes). In F1, enter "=IF(ISERROR(FIND("X",C1)),C1,D1)" (minus the quotes). Fill the whole E and F column. E will now contain the dimensions and F will contain the gauge.

Note: I assume there is no X in a value in the gauge column. If there is, you'll have to modify this slightly, but the idea behind it will still work.

If so, import it into Excel. You would now have data in four columns (A, B, C, D). Then go to E1 and enter "=IF(ISERROR(FIND("X",C1)),D1,C1)" (minus the quotes). In F1, enter "=IF(ISERROR(FIND("X",C1)),C1,D1)" (minus the quotes). Fill the whole E and F column. E will now contain the dimensions and F will contain the gauge.

Note: I assume there is no X in a value in the gauge column. If there is, you'll have to modify this slightly, but the idea behind it will still work.

#

**20**DVD Talk Limited Edition

Thread Starter

Originally Posted by

**Jeremy517**Am I correct that sometimes you are seeing, for example, "SS,304,16GA,48X96" and sometimes you see "SS,304,48X96,16GA"?

If so, import it into Excel. You would now have data in four columns (A, B, C, D). Then go to E1 and enter "=IF(ISERROR(FIND("X",C1)),D1,C1)" (minus the quotes). In F1, enter "=IF(ISERROR(FIND("X",C1)),C1,D1)" (minus the quotes). Fill the whole E and F column. E will now contain the dimensions and F will contain the gauge.

Note: I assume there is no X in a value in the gauge column. If there is, you'll have to modify this slightly, but the idea behind it will still work.

If so, import it into Excel. You would now have data in four columns (A, B, C, D). Then go to E1 and enter "=IF(ISERROR(FIND("X",C1)),D1,C1)" (minus the quotes). In F1, enter "=IF(ISERROR(FIND("X",C1)),C1,D1)" (minus the quotes). Fill the whole E and F column. E will now contain the dimensions and F will contain the gauge.

Note: I assume there is no X in a value in the gauge column. If there is, you'll have to modify this slightly, but the idea behind it will still work.

Here is the list I'm working with. This might explain a little better than I am.

Excel File

Columns C & D are what namja's formula came up with. E & F is what I'm trying to get the Width and Length separated. I think I pretty much have column B (item description column) sorted into

**Material**,

**Gauge**,

**Length**X

**Width**for each cell. There may be a few I need to hunt down though.

*Last edited by kantonburg; 01-23-08 at 03:12 PM.*

#

**21**New Member

Join Date: Jul 2005

Location: Perth, Australia

Posts: 4

Likes: 0

Received 0 Likes
on
0 Posts

Is this what you are after?

http://members.iinet.net.au/~vonrotz/excel/sort.xls

I'm not the best at Excel formulas, by the time I come up with one I would have finished 20 times already using simple sorts.

I use Excel 2k7 so my method might be a little different to those using 2k3.

First I selected the cells in question in B4-1032. Then applied a text to columns using comma delimited with all columns output to text, and desination as $C$4 (you should in general get 3 columns).

Now I looked at the remaining cells in E, most were separated by either X or x, so I tried another delimited sort (using a value), but it didn't work out as expected due to two sorts required for X and x and they replaced some other cells, so I skimmed the data and decided to run a fixed width sort. I chose this since most of the width data was only two digits. I placed two break lines, one at 2 and one at 3. Caught 99% of the data and separated them into 3 columns, width, x's and Length

Spent 5mins cleaning up some cells running down the table, probably about 50 or so with odd data in a few places.

Deleted all the F column (all x's) and put the length column (G) back into place. Converted Width and Length back to number format and that's about it.

Took about 10mins all up.

http://members.iinet.net.au/~vonrotz/excel/sort.xls

I'm not the best at Excel formulas, by the time I come up with one I would have finished 20 times already using simple sorts.

I use Excel 2k7 so my method might be a little different to those using 2k3.

First I selected the cells in question in B4-1032. Then applied a text to columns using comma delimited with all columns output to text, and desination as $C$4 (you should in general get 3 columns).

Now I looked at the remaining cells in E, most were separated by either X or x, so I tried another delimited sort (using a value), but it didn't work out as expected due to two sorts required for X and x and they replaced some other cells, so I skimmed the data and decided to run a fixed width sort. I chose this since most of the width data was only two digits. I placed two break lines, one at 2 and one at 3. Caught 99% of the data and separated them into 3 columns, width, x's and Length

Spent 5mins cleaning up some cells running down the table, probably about 50 or so with odd data in a few places.

Deleted all the F column (all x's) and put the length column (G) back into place. Converted Width and Length back to number format and that's about it.

Took about 10mins all up.

#

**22**DVD Talk Limited Edition

Thread Starter

Originally Posted by

**nostaw**Is this what you are after?

http://members.iinet.net.au/~vonrotz/excel/sort.xls

I'm not the best at Excel formulas, by the time I come up with one I would have finished 20 times already using simple sorts.

I use Excel 2k7 so my method might be a little different to those using 2k3.

First I selected the cells in question in B4-1032. Then applied a text to columns using comma delimited with all columns output to text, and desination as $C$4 (you should in general get 3 columns).

Now I looked at the remaining cells in E, most were separated by either X or x, so I tried another delimited sort (using a value), but it didn't work out as expected due to two sorts required for X and x and they replaced some other cells, so I skimmed the data and decided to run a fixed width sort. I chose this since most of the width data was only two digits. I placed two break lines, one at 2 and one at 3. Caught 99% of the data and separated them into 3 columns, width, x's and Length

Spent 5mins cleaning up some cells running down the table, probably about 50 or so with odd data in a few places.

Deleted all the F column (all x's) and put the length column (G) back into place. Converted Width and Length back to number format and that's about it.

Took about 10mins all up.

http://members.iinet.net.au/~vonrotz/excel/sort.xls

I'm not the best at Excel formulas, by the time I come up with one I would have finished 20 times already using simple sorts.

I use Excel 2k7 so my method might be a little different to those using 2k3.

First I selected the cells in question in B4-1032. Then applied a text to columns using comma delimited with all columns output to text, and desination as $C$4 (you should in general get 3 columns).

Now I looked at the remaining cells in E, most were separated by either X or x, so I tried another delimited sort (using a value), but it didn't work out as expected due to two sorts required for X and x and they replaced some other cells, so I skimmed the data and decided to run a fixed width sort. I chose this since most of the width data was only two digits. I placed two break lines, one at 2 and one at 3. Caught 99% of the data and separated them into 3 columns, width, x's and Length

Spent 5mins cleaning up some cells running down the table, probably about 50 or so with odd data in a few places.

Deleted all the F column (all x's) and put the length column (G) back into place. Converted Width and Length back to number format and that's about it.

Took about 10mins all up.

Thanks

#

**23**New Member

Join Date: Jul 2005

Location: Perth, Australia

Posts: 4

Likes: 0

Received 0 Likes
on
0 Posts

Oh, easier way, pull a find and replace. Replace all lower case x's with X. Then instead of doing a fixed width sort, you can simply do another delimited sort on colum E using X as the variable. A lot easier.

#

**24**DVD Talk Limited Edition

Thread Starter

Originally Posted by

**nostaw**Oh, easier way, pull a find and replace. Replace all lower case x's with X. Then instead of doing a fixed width sort, you can simply do another delimited sort on colum E using X as the variable. A lot easier.

#

**25**Member

Join Date: Apr 2006

Location: London, England

Posts: 156

Likes: 0

Received 0 Likes
on
0 Posts

Don't know if you're still looking for an answer on this but you might try the VBA Split function.

varArray = Split([Cell],",")

The varArray will now contain each of the original strings broken on the comma. You could repeat that to split the Width x Height column as well

varArray2 = Split(varArray(3),"X")

That would would with both Upper and Lower case "X" values if you're treating them the same (Options at the top of the VBA) otherwise you could do a replace i.e.

varArray2 = Split(replace(varArray(3),"x","X"),"X")

varArray = Split([Cell],",")

The varArray will now contain each of the original strings broken on the comma. You could repeat that to split the Width x Height column as well

varArray2 = Split(varArray(3),"X")

That would would with both Upper and Lower case "X" values if you're treating them the same (Options at the top of the VBA) otherwise you could do a replace i.e.

varArray2 = Split(replace(varArray(3),"x","X"),"X")