43 replies to this topic

[rajncajn]

Multiple rows of data that read similar to below in each cell:

SD-V-324
SEC DRAINAGE EDUC
BILGE SUCT PRESS GA ROOT

What I want to do is break the data up. the "SD-V-324" is an identification number and the "SEC DRAINAGE EDUC BILGE SUCT PRESS GA ROOT" is a description. I want each in it's own cell. Basic text-to-columns won't work, because there is no delimiter that I can safely use without having to piece back together a lot of data and using it with a fixed width is very limited as well because the trailing numbers (324) vary between 1 & 2000.

[gbpfan1231]

rajncajn, on 6/6/11 3:59pm, said:

Multiple rows of data that read similar to below in each cell:

SD-V-324
SEC DRAINAGE EDUC
BILGE SUCT PRESS GA ROOT

What I want to do is break the data up. the "SD-V-324" is an identification number and the "SEC DRAINAGE EDUC BILGE SUCT PRESS GA ROOT" is a description. I want each in it's own cell. Basic text-to-columns won't work, because there is no delimiter that I can safely use without having to piece back together a lot of data and using it with a fixed width is very limited as well because the trailing numbers (324) vary between 1 & 2000.

Do you have a list anywhere that would have the same items with only the identification number?  You could maybe line them up and then use the MID and LEN functions to just grab the ID and description from the main cell.  I am guessing you don't have that though??

[gbpfan1231]

Actually - just thought of something?  Does your Identification number ever have a space in it?  If not then in your example you could write a formula to tell you that the 9th character is the first space - use the FIND function.  You then know that 9 minus one = how many characters your ID number is and you can then use =LEFT to get that.

You can also use the LEN function to count how many characters are in the complete string and then use the =RIGHT function using the LEN minus the 9 (from the FIND function) to bring back the correct number of characters for your description.

If I confused you could PM and you could e-mail me the file and I could help.  Or I could give you a phone number to call and discuss which is probably easier.

[rajncajn]

gbpfan1231, on 6/6/11 4:21pm, said:

Do you have a list anywhere that would have the same items with only the identification number?  You could maybe line them up and then use the MID and LEN functions to just grab the ID and description from the main cell.  I am guessing you don't have that though??

No, this is all new data that was given to us by a builder and we have to reformat it to fit our database. Probably around 4000 items & each has it's own unique ID number and extremely few of the descriptions will match.

[rajncajn]

gbpfan1231, on 6/6/11 4:26pm, said:

Actually - just thought of something?  Does your Identification number ever have a space in it?  If not then in your example you could write a formula to tell you that the 9th character is the first space - use the FIND function.  You then know that 9 minus one = how many characters your ID number is and you can then use =LEFT to get that.

You can also use the LEN function to count how many characters are in the complete string and then use the =RIGHT function using the LEN minus the 9 (from the FIND function) to bring back the correct number of characters for your description.

If I confused you could PM and you could e-mail me the file and I could help.  Or I could give you a phone number to call and discuss which is probably easier.

ID numbers never have a space, but they vary widely in length of characters some will also trail in a letter such as FM-V-12B. If there is a function to tell it to only break it after the first space then that might work.

[rajncajn]

I'll also add in an extra twist... As you may notice in my example above, there are line breaks within the cell. However, in the data that we have been given to work with, some have line breaks & some do not. So even if one idea works, it may not work for everything. But at least it will get me through this mess a little quicker.  

[gbpfan1231]

rajncajn, on 6/6/11 4:31pm, said:

ID numbers never have a space, but they vary widely in length of characters some will also trail in a letter such as FM-V-12B. If there is a function to tell it to only break it after the first space then that might work.

There is - try this...  Assume your example in your original post is in cell A1.  in B1 type the following:

=FIND(" ",A1,1)  the result should be 9 I believe for your example.  Then in Cell C1 do the following:

=LEFT(A1,(B1-1)   this will then bring back the first 8 characters of A1 by taking the result of cell B1 which is 9 and subtracting 1 to get you the 8.

Capiche?

[rajncajn]

Here's a few more examples for good measure

JPT-V-300 JP-5 XFR DISCH
TO FILL MAIN


JPT-V-306
RESCUE BOAT ISOLATION


JPS-F-30
JP-5 SERVICE
FILTER / SEPARATOR

Edited by rajncajn, 06 June 2011 - 04:39 PM.

[gbpfan1231]

rajncajn, on 6/6/11 4:39pm, said:

Here's a few more examples for good measure

JPT-V-300 JP-5 XFR DISCH
TO FILL MAIN


JPT-V-306
RESCUE BOAT ISOLATION


JPS-F-30
JP-5 SERVICE
FILTER / SEPARATOR

Do they all show up wrapped like that or do you have the column width too small so it looks like it is wrapping?  If it is actually a carriage return entered into the cell to make it wrap try using the CLEAN function - I just tried it and it got rid of the carriage return and left the spaces which I believe you need.

[gbpfan1231]

rajncajn, on 6/6/11 4:39pm, said:

Here's a few more examples for good measure

JPT-V-300 JP-5 XFR DISCH
TO FILL MAIN


JPT-V-306
RESCUE BOAT ISOLATION


JPS-F-30
JP-5 SERVICE
FILTER / SEPARATOR

Just sent you a PM.

[rajncajn]

gbpfan1231, on 6/6/11 4:47pm, said:

Do they all show up wrapped like that or do you have the column width too small so it looks like it is wrapping?  If it is actually a carriage return entered into the cell to make it wrap try using the CLEAN function - I just tried it and it got rid of the carriage return and left the spaces which I believe you need.

I tried the formula & it works perfect for the one's like in the JPT-V-300 example where part of the description is on the same line and there is a space. However, a good majority of the items have the carriage return instead. If I do the CLEAN function will it replace the carriage return with a space or will it just remove it with me ending up with something like this:

JPT-V-306RESCUE BOAT ISOLATION

[lennykravitz2004]

How 'bout this one:

=SUBSTITUTE(F5,"*",",")

Copy and paste in the address bar for the cell.  BEFORE exiting cell, highlight the * and press alt+enter, then tab out of the cell.  Your values should now be replaced with commas.

Copy all cells, then Paste Special.

Text to Columns with commas.

[gbpfan1231]

rajncajn, on 6/6/11 4:54pm, said:

I tried the formula & it works perfect for the one's like in the JPT-V-300 example where part of the description is on the same line and there is a space. However, a good majority of the items have the carriage return instead. If I do the CLEAN function will it replace the carriage return with a space or will it just remove it with me ending up with something like this:

JPT-V-306RESCUE BOAT ISOLATION

Should just remove the carriage return - don't believe it will add a space.

What Lenny said might work but do you have more than one carriage return?  Then the text to columns will still create two or more columns for your one description.  Instead of replacing the carriage return with a comma like he said replace it with nothing - so instead of "," just use "" (basically two quotes stuck together.

[rajncajn]

lennykravitz2004, on 6/6/11 4:58pm, said:

How 'bout this one:



Copy and paste in the address bar for the cell.  BEFORE exiting cell, highlight the * and press alt+enter, then tab out of the cell.  Your values should now be replaced with commas.

Copy all cells, then Paste Special.

Text to Columns with commas.

Actually, it works in conjunction with the formula gbpfan provided. If I change it to commas then it breaks part of the description as well. But if I use spaces instead then I can use his formula ro separate them. Now all I need is the formula to pull just the description & I'm all set.

I need a beer...  

[rajncajn]

Sweet, it's working beautifully! This is what I start out with:

JPS-F-30
JP-5 SERVICE
FILTER / SEPARATOR

This is what I end up with using Lenny's formula:
=SUBSTITUTE(A3,"*"," ")  where "*" is the carriage return

JPS-F-30 JP-5 SERVICE FILTER / SEPARATOR

And this is what I get using gbpfan's formula:
=FIND(" ",B3,1)
=LEFT(B3,(C3-1))

JPS-F-30


Now all I need is the formula to get me this:

JP-5 SERVICE FILTER / SEPARATOR

[gbpfan1231]

rajncajn, on 6/6/11 5:36pm, said:

Sweet, it's working beautifully! This is what I start out with:

JPS-F-30
JP-5 SERVICE
FILTER / SEPARATOR

This is what I end up with using Lenny's formula:
=SUBSTITUTE(A3,"*"," ")  where "*" is the carriage return

JPS-F-30 JP-5 SERVICE FILTER / SEPARATOR

And this is what I get using gbpfan's formula:
=FIND(" ",B3,1)
=LEFT(B3,(C3-1))

JPS-F-30


Now all I need is the formula to get me this:

JP-5 SERVICE FILTER / SEPARATOR

In a cell use the formula =LEN(A3) this will give you the total number of characters in the complete string - you now know the length of the entire cell and you know the length of the item which is the result of the FIND formula.  So you then subtract the FIND result from the LEN result and you have the length of the Description so then use the =RIGHT function to bring it in.

=RIGHT(B3,Len(B3)-FIND(" ",B3,1))

I think the above should do it if I have the cells right - you can do the formulas in multiple columns if it makes it easier than embedding multiple formulas into one formula.

[rajncajn]

That did the trick, thanks a ton guys. What will take me probably a couple of hours to complete likely would have taken me at least a couple of days to put together the old fashioned way.   When I'm not as pressed for time I'll have to dig into the formulas a little more so I'll know exactly what they did. I've got the gist of it, but I like to take things apart & see how they really work.

[gbpfan1231]

rajncajn, on 6/6/11 8:12pm, said:

That did the trick, thanks a ton guys. What will take me probably a couple of hours to complete likely would have taken me at least a couple of days to put together the old fashioned way.   When I'm not as pressed for time I'll have to dig into the formulas a little more so I'll know exactly what they did. I've got the gist of it, but I like to take things apart & see how they really work.

[rajncajn]

Bump with another

If cell B has any of the same info as cell A then highlight it. For instance:

|My name is Mud | Your name is Mud |

becomes

My name is Mud | Your name is Mud |

[gbpfan1231]

rajncajn, on 7/14/11 10:33am, said:

Bump with another

If cell B has any of the same info as cell A then highlight it. For instance:

|My name is Mud | Your name is Mud |

becomes

My name is Mud | Your name is Mud |

YIKES!!  Define "same info"  This sounds impossible.

[rajncajn]

gbpfan1231, on 7/14/11 12:35pm, said:

YIKES!!  Define "same info"  This sounds impossible.

the first column of cells (column A) has a series of numbers in it such as "2-13-4". I have to verify that the cell next to it in column B has that same series of numbers in it, but that cell will also include a description such as "2-13-4 DRAIN VALVE." If it does then I want some type of marker to tell me that those cells do in fact have the same series of numbers in them such even though the cells in column B includes additional text.Some type of marker such as a highlight so that I can filter them out and mark the one's that do not match once the entire spreadsheet has been checked.

[Big Country]

Using your assumption of column A containing the text you are looking for and B being the details description, put the following formula into column C;

=ISNUMBER(FIND(A2,B2))   This will give you a True if the contents of column A are found in the contents of column B, however, note that it is case sensitive and must be an exact match

ETA: Once you do that and copy the formula for the entirety of column C, simply filter for "FALSE" on column C and you will have all of the items that do not match

ETA2: If you don't need the search to be case sensitive, use this formula instead: =ISNUMBER(SEARCH(A2,B2))

Edited by Big Country, 14 July 2011 - 04:09 PM.

[rajncajn]

Big Country, on 7/14/11 4:03pm, said:

Using your assumption of column A containing the text you are looking for and B being the details description, put the following formula into column C;

=ISNUMBER(FIND(A2,B2))   This will give you a True if the contents of column A are found in the contents of column B, however, note that it is case sensitive and must be an exact match

ETA: Once you do that and copy the formula for the entirety of column C, simply filter for "FALSE" on column C and you will have all of the items that do not match

ETA2: If you don't need the search to be case sensitive, use this formula instead: =ISNUMBER(SEARCH(A2,B2))

That works.

[gbpfan1231]

Big Country, on 7/14/11 4:03pm, said:

Using your assumption of column A containing the text you are looking for and B being the details description, put the following formula into column C;

=ISNUMBER(FIND(A2,B2))   This will give you a True if the contents of column A are found in the contents of column B, however, note that it is case sensitive and must be an exact match

ETA: Once you do that and copy the formula for the entirety of column C, simply filter for "FALSE" on column C and you will have all of the items that do not match

ETA2: If you don't need the search to be case sensitive, use this formula instead: =ISNUMBER(SEARCH(A2,B2))

Impressive - Thanks - you have taught me something that I may use.  Appreciate it!!

[Big Country]

gbpfan1231, on 7/14/11 2:55pm, said:

Impressive - Thanks - you have taught me something that I may use.  Appreciate it!!

Wish I could take the credit, but it was the result of a successful Google search and making some minor modifications to the samples presented.

But, this may well be something that I can use in the future, especially in some FF related research.