Go Back  DVD Talk Forum > General Discussions > Tech Talk
Reload this Page >

Microsoft Excel formula question

Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

Microsoft Excel formula question

Old 01-18-08, 01:29 PM
  #1  
DVD Talk Limited Edition
Thread Starter
 
Join Date: Jun 2000
Location: BV VA
Posts: 6,078
Received 0 Likes on 0 Posts
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

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

Width

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.
Old 01-18-08, 03:58 PM
  #2  
Senior Member
 
Join Date: Sep 2007
Posts: 561
Likes: 0
Received 0 Likes on 0 Posts
Is the number of digits in each field always the same?

2digits,3digits,2digits(GA),2digits(X)2digits ??
Old 01-18-08, 04:30 PM
  #3  
Stealth Moderator
 
namja's Avatar
 
Join Date: Oct 1999
Location: In Transit, HQ
Posts: 25,038
Received 15 Likes on 8 Posts
Originally Posted by jonnyquest
Is the number of digits in each field always the same?

2digits,3digits,2digits(GA),2digits(X)2digits ??
Yeah, this is the first questions I was going to ask. If this is the case, then this would be very simple (use LEFT, MID, RIGHT functions). If they are all different, then it gets a little trickier.
Old 01-18-08, 04:50 PM
  #4  
Stealth Moderator
 
namja's Avatar
 
Join Date: Oct 1999
Location: In Transit, HQ
Posts: 25,038
Received 15 Likes on 8 Posts
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.
Old 01-19-08, 11:59 AM
  #5  
DVD Talk Limited Edition
Thread Starter
 
Join Date: Jun 2000
Location: BV VA
Posts: 6,078
Received 0 Likes on 0 Posts
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.
Old 01-19-08, 12:02 PM
  #6  
DVD Talk Limited Edition
Thread Starter
 
Join Date: Jun 2000
Location: BV VA
Posts: 6,078
Received 0 Likes on 0 Posts
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.
wow namja,

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
Old 01-19-08, 08:08 PM
  #7  
Senior Member
 
Join Date: Jun 2001
Location: dc-md-va
Posts: 959
Likes: 0
Received 0 Likes on 0 Posts
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?
Old 01-20-08, 08:49 AM
  #8  
DVD Talk Limited Edition
Thread Starter
 
Join Date: Jun 2000
Location: BV VA
Posts: 6,078
Received 0 Likes on 0 Posts
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?
You know I'm not sure. I'll give that a whirl. That also could be an output option that I didn't even think about.
Old 01-20-08, 09:00 AM
  #9  
Senior Member
 
Join Date: Jun 2001
Location: dc-md-va
Posts: 959
Likes: 0
Received 0 Likes on 0 Posts
I regularly output from CrystalReports into .xls and .doc. I recall using .csv on a few occasions now that I think about it. It should be under Print, Export.
Old 01-20-08, 09:26 AM
  #10  
DVD Talk Hall of Fame
 
Join Date: Aug 2002
Location: Triangle, NC, USA
Posts: 9,059
Received 23 Likes on 16 Posts
text to columns, using 'comma' as the delimiting character?
Old 01-20-08, 11:52 AM
  #11  
Stealth Moderator
 
namja's Avatar
 
Join Date: Oct 1999
Location: In Transit, HQ
Posts: 25,038
Received 15 Likes on 8 Posts
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.
Old 01-22-08, 12:33 AM
  #12  
Stealth Moderator
 
namja's Avatar
 
Join Date: Oct 1999
Location: In Transit, HQ
Posts: 25,038
Received 15 Likes on 8 Posts
So ... did you try it? Did it work? Or did you not go into work today b/c it's holiday?
Old 01-22-08, 06:05 AM
  #13  
DVD Talk Limited Edition
Thread Starter
 
Join Date: Jun 2000
Location: BV VA
Posts: 6,078
Received 0 Likes on 0 Posts
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?
Sorry I forgot to post back. For some reason I thought I had. The data, as it went down the sheet, was shuffled in order. So sometimes the material ended up in the gauge column and vice versa. The information itself is manually entered in macola and had been over the years so the description column (what we're trying to separate) was entered by different people, hence the mixup of information.

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.
Old 01-22-08, 08:38 PM
  #14  
DVD Talk Hall of Fame
 
Duran's Avatar
 
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.
Old 01-23-08, 01:05 PM
  #15  
DVD Talk Limited Edition
Thread Starter
 
Join Date: Jun 2000
Location: BV VA
Posts: 6,078
Received 0 Likes on 0 Posts
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.

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.
Old 01-23-08, 01:31 PM
  #16  
DVD Talk Legend
 
Join Date: Aug 2000
Location: Newberg, OR
Posts: 17,311
Received 6 Likes on 6 Posts
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.
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.
Old 01-23-08, 02:09 PM
  #17  
DVD Talk Hall of Fame
 
Duran's Avatar
 
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.
Old 01-23-08, 02:30 PM
  #18  
Moderator
 
wendersfan's Avatar
 
Join Date: Jun 2002
Location: America!
Posts: 33,724
Received 96 Likes on 68 Posts
Originally Posted by dtcarson
text to columns, using 'comma' as the delimiting character?
That's what I'd do. Text to columns if a very useful feature.
Old 01-23-08, 02:47 PM
  #19  
DVD Talk Limited Edition
Thread Starter
 
Join Date: Jun 2000
Location: BV VA
Posts: 6,078
Received 0 Likes on 0 Posts
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.
Sweet. I'm at home now. I'll give it a shot in a few minutes. Post back in a bit.
Old 01-23-08, 03:08 PM
  #20  
DVD Talk Limited Edition
Thread Starter
 
Join Date: Jun 2000
Location: BV VA
Posts: 6,078
Received 0 Likes on 0 Posts
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.
I re-read this. Actually that line of data is in one cell. No matter how I export out of crystal reports that Item Description is in one cell.

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,LengthXWidth 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.
Old 01-24-08, 07:42 AM
  #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.
Old 01-24-08, 07:49 AM
  #22  
DVD Talk Limited Edition
Thread Starter
 
Join Date: Jun 2000
Location: BV VA
Posts: 6,078
Received 0 Likes on 0 Posts
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.
That seems to be exactly what I'm looking for. Thank you. Now I'll give it a whirl myself just so I can replicate.

Thanks
Old 01-24-08, 08:00 AM
  #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.
Old 01-24-08, 08:36 AM
  #24  
DVD Talk Limited Edition
Thread Starter
 
Join Date: Jun 2000
Location: BV VA
Posts: 6,078
Received 0 Likes on 0 Posts
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.
Thats actually the first thing I did after I read your other post. Now I'm just trying to figure out how to do a text to columns comma delimited sort for a row. I wonder if 2k3 has this feature?
Old 01-29-08, 08:15 AM
  #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")

Thread Tools
Search this Thread

Archive - Advertising - Cookie Policy - Privacy Statement - Terms of Service - Do Not Sell My Personal Information -

Copyright 2021 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.