Text to columns question

M

Mr BT

How can I run a text to column on the first space in the cell? I also want
to run this script with directions to separate on the second space rather
than the first.
Thank you for the help

Mr BT
 
B

Bernard Liengme

Your sue of the word "script" suggests you want a VBA solution.
But here are some non-VBA pointers:
1) are you aware of the feature Data }Text to Columns ?
2) If A1 has the value "abc xyz" (without quotes) then
=LEFT(A1,FIND(" ",A1)-1) will return "abc" and =MID(A1,FIND(" ",A1)+1,256)
will return "xyz"
What to want to happen with "abc klmnop xyz"? Do you want three separate
parts, or two?
best wishes
 
M

Mr BT

This is great. And yes I would like to know how you would separate a fields
(each space) for the following example, "abc klmnop xyz 123 456 789"?


=MID(A1,FIND(" ",A1)+1,256)
Can I assume 256 from the formula above is for 256 characters?

I'm also using Excel 2007 if that matters.

Thanks again.

Mr BT
 
B

Bernard Liengme

Hello:
1) =MID(A1,FIND(" ",A1)+1,256)
Can I assume 256 from the formula above is for 256 characters?
Click to expand...
256 is just a big number that should work with anything you have in a cell.
2)
"abc klmnop xyz 123 456 789"?
Click to expand...
This could be done with VBA but the easiest method is Text-to-column
specifying that the delimiter is Space.
best wishes
 
I

ilia

Text-to-columns is indeed an effective method. However, you can use
formulas to make this dynamic. This assumes you have at least one
space.

Assume your data is in column A, and row 1 contains headers.

B1: =LEFT($A2,FIND(" ",$A2,1)-1)
C1: =IF(LEN($A2)-LEN(SUBSTITUTE($A2,"
",""))<(COLUMN()-2),"",IF(LEN($A2)-LEN(SUBSTITUTE($A2,"
",""))<(COLUMN()-1),RIGHT($A2,LEN($A2)-FIND(CHAR(200),SUBSTITUTE($A2,"
",CHAR(200),LEN($A2)-LEN(SUBSTITUTE($A2,"
",""))))),MID($A2,FIND(CHAR(200),SUBSTITUTE($A2,"
",CHAR(200),COLUMN()-2))+1,FIND(CHAR(200),SUBSTITUTE($A2,"
",CHAR(200),COLUMN()-1))-FIND(CHAR(200),SUBSTITUTE($A2,"
",CHAR(200),COLUMN()-2))-1)))

Copy the formula in C1 as far over to the right as you expect to have
spaces. Next, copy down as far as you need to go to accomodate your
data. It relies on column C being the value between 1st and 2nd
space; if there is no third space, it will return the characters to
the right of the first space if there is no second space, and
analogously for later spaces.

For 50,000 rows and accounting for up to 7 spaces, this took well
under 10 seconds to calculate in Excel 2007. Maximum number of
function nesting is 6, so this should work in earlier versions of
Excel. Finally, the C1 formula assumes that none of your strings will
have the È character, which has the ASCII code of 200.

Hope that's useful.
 
M

Mr BT

Thank you all for your help.
Text to columns won't help with this issue because I don't want to separate
the cell from all of its spaces, I want to be able to select from the cell
( and other cells in the same column ) a specific number of spaces.
For example, (which I realize could have helped earlier)
I have the following in cell A2 .... Product Code followed by the Product
Name and Description, then another Product Part Code and the Cost.
All of this appears in one cell when I'm pasting it from Adobe Reader to
Excel. There are only spaces separating them. I don't want the description
broken up from everything else.
I want to tell the cells adjacent to A2 to separate it at the space after
the product code(space 1) then again in between the name and description
(space 7 or 8 or 9. Its never the same) then again at the product part code
(space 2 from the right) and the cost (space 1 from the right)


This is just an example. I am using it for information that is not
copy/pasted from Adobe Reader, but this was my best example.
Sorry, one more thing: consecutive spaces in original string will
result in a blank.
 
R

Ron Rosenfeld

Thank you all for your help.
Text to columns won't help with this issue because I don't want to separate
the cell from all of its spaces, I want to be able to select from the cell
( and other cells in the same column ) a specific number of spaces.
For example, (which I realize could have helped earlier)
I have the following in cell A2 .... Product Code followed by the Product
Name and Description, then another Product Part Code and the Cost.
All of this appears in one cell when I'm pasting it from Adobe Reader to
Excel. There are only spaces separating them. I don't want the description
broken up from everything else.
I want to tell the cells adjacent to A2 to separate it at the space after
the product code(space 1) then again in between the name and description
(space 7 or 8 or 9. Its never the same) then again at the product part code
(space 2 from the right) and the cost (space 1 from the right)
Click to expand...

Here is a UDF that I think will do what you describe. However, if your
different sections have unique ways of being identified, other than their
location as determined by <space>s, there might be another way to do this.

In any event, the function that you enter is:

=SepAtSpace(cell_ref,SP1,SP2)

SP1 is the first space where you want to start the extraction, and should be
entered as 0 (zero) if you want to start at the beginning of the string.

SP2 is the last space you wish to extract, or the number of spaces + 1 if you
want to extract to the end of the string.

The function counts multiple spaces between words as a single space.

The function will return a blank if
SP1 >= SP2
SP1 or SP2 are not in the range 0 to number of spaces + 1

To enter the UDF, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

=====================================
Option Explicit

Function SepAtSpace(str As String, SP1 As Long, SP2 As Long) As String
Dim re As Object
Dim mc As Object
Dim sPat As String
Dim i As Long

Set re = CreateObject("vbscript.regexp")
'create sPat
sPat = "^"
sPat = sPat & "(\S+\s+){" & SP1 & "}"
sPat = sPat & "((\S+\s+){" & SP2 - SP1 - 1 & "}\S+(\s|$))"

re.Pattern = sPat

If re.test(str) = True Then
Set mc = re.Execute(str)
SepAtSpace = mc(0).submatches(1)
End If

End Function
=================================
--ron
 
R

Ron Rosenfeld

Thank you all for your help.
Text to columns won't help with this issue because I don't want to separate
the cell from all of its spaces, I want to be able to select from the cell
( and other cells in the same column ) a specific number of spaces.
For example, (which I realize could have helped earlier)
I have the following in cell A2 .... Product Code followed by the Product
Name and Description, then another Product Part Code and the Cost.
All of this appears in one cell when I'm pasting it from Adobe Reader to
Excel. There are only spaces separating them. I don't want the description
broken up from everything else.
I want to tell the cells adjacent to A2 to separate it at the space after
the product code(space 1) then again in between the name and description
(space 7 or 8 or 9. Its never the same) then again at the product part code
(space 2 from the right) and the cost (space 1 from the right)
Click to expand...

As I reread your problem description, it occurs to me that the big problem is
to separate the Product Name from the Product Description. It is relatively
trivial to separate out the

Product Code: First Word
Product Part Code: Next-to-last Word
Cost: Last Word

If you have long lists, it will be tedious to have to manually enter the start
and end space numbers for each of those entries.

How do you differentiate the Name from the Description when you inspect the
string manually?

Obviously, the Product Name starts with the Second Word and the Product
Description ends with the third-from-last word. So we just need to figure out
how to separate these two.
--ron
 
Top