How to split text?

[Jmbostock]

I have an excel spreadsheet created from an external database program.
Unfortunatly it didn't format the cells or split the text into
different columns.

An example of which is :

000105 Ritz Camera Center

This is all in one cell.

What i'm trying to do is split the cell in 2, having the Vendor Code in
one cell (000105) and the vendor name in the other.

Ideally i'd like to have a macro split the cell at the first blank
space, but i'm at a loss on how to do that. If anyone has an idea of
how i can do this i'd really appreciate the help. Been hitting my head
against a wall on this one.

James

 

[Don Guillett]

data>text to columns>

 

[A.W.J. Ales]

Jmbostock,

Have you considered to use : Data / Text to Columns...

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Jmbostock]

So simple. I feel like a total idiot for missing that one.

Thanks

 

[Mschndrt]

Suggest using Left and Right Formulas

Martin

-----Original Message-----
Jmbostock,

Have you considered to use : Data / Text to Columns...

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *




.

 

[Dana DeLouis]

Because you want to split it in two, you may want to consider a Macro.

Sub Demo()
Dim s As String
s = "000105 Ritz Camera Center"
[B1:C1] = Split(s, Space(1), 2)
End Sub

 

[Don Guillett]

Using that method, a multiple condition could be
Sub Splitcells()
x = 1
For Each s In selection 'Range("a2:a22")
Range(Cells(x, 2), Cells(x, 3)) = _
Split(s, Space(1), 2)
x = x + 1
Next
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

Because you want to split it in two, you may want to consider a Macro.

Sub Demo()
Dim s As String
s = "000105 Ritz Camera Center"
[B1:C1] = Split(s, Space(1), 2)
End Sub

 

[Tom Ogilvy]

as written, you put the results of processing the first cell in the
selection in row 1 (which would be bad if row 1 is not the location of the
first cell in the selection), so you could both reduce the amount of code
and be more consistent with

Sub Splitcells()
For Each s In selection \
Range(Cells(s.row, 2), Cells(s.row, 3)) = _
Split(s, Space(1), 2)
Next
End Sub

--
Regards,
Tom Ogilvy

Using that method, a multiple condition could be
Sub Splitcells()
x = 1
For Each s In selection 'Range("a2:a22")
Range(Cells(x, 2), Cells(x, 3)) = _
Split(s, Space(1), 2)
x = x + 1
Next
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

Because you want to split it in two, you may want to consider a Macro.

Sub Demo()
Dim s As String
s = "000105 Ritz Camera Center"
[B1:C1] = Split(s, Space(1), 2)
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

I have an excel spreadsheet created from an external database program.
Unfortunatly it didn't format the cells or split the text into
different columns.

An example of which is :

000105 Ritz Camera Center

This is all in one cell.

What i'm trying to do is split the cell in 2, having the Vendor Code in
one cell (000105) and the vendor name in the other.

Ideally i'd like to have a macro split the cell at the first blank
space, but i'm at a loss on how to do that. If anyone has an idea of
how i can do this i'd really appreciate the help. Been hitting my head
against a wall on this one.

James

 

[Tom Ogilvy]

Introduced a stray character when deleting the commented out
'Range("A2:A22")

Sub Splitcells()
For Each s In selection
Range(Cells(s.row, 2), Cells(s.row, 3)) = _
Split(s, Space(1), 2)
Next
End Sub

--
Regards,
Tom Ogilvy

as written, you put the results of processing the first cell in the
selection in row 1 (which would be bad if row 1 is not the location of the
first cell in the selection), so you could both reduce the amount of code
and be more consistent with

Sub Splitcells()
For Each s In selection \
Range(Cells(s.row, 2), Cells(s.row, 3)) = _
Split(s, Space(1), 2)
Next
End Sub

--
Regards,
Tom Ogilvy

Using that method, a multiple condition could be
Sub Splitcells()
x = 1
For Each s In selection 'Range("a2:a22")
Range(Cells(x, 2), Cells(x, 3)) = _
Split(s, Space(1), 2)
x = x + 1
Next
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

Because you want to split it in two, you may want to consider a Macro.

Sub Demo()
Dim s As String
s = "000105 Ritz Camera Center"
[B1:C1] = Split(s, Space(1), 2)
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


I have an excel spreadsheet created from an external database program.
Unfortunatly it didn't format the cells or split the text into
different columns.

An example of which is :

000105 Ritz Camera Center

This is all in one cell.

What i'm trying to do is split the cell in 2, having the Vendor Code in
one cell (000105) and the vendor name in the other.

Ideally i'd like to have a macro split the cell at the first blank
space, but i'm at a loss on how to do that. If anyone has an idea of
how i can do this i'd really appreciate the help. Been hitting my head
against a wall on this one.

James

 

[Don Guillett]

OK. I like em short.

--
Don Guillett
SalesAid Software
(e-mail address removed)

as written, you put the results of processing the first cell in the
selection in row 1 (which would be bad if row 1 is not the location of the
first cell in the selection), so you could both reduce the amount of code
and be more consistent with

Sub Splitcells()
For Each s In selection \
Range(Cells(s.row, 2), Cells(s.row, 3)) = _
Split(s, Space(1), 2)
Next
End Sub

--
Regards,
Tom Ogilvy

Using that method, a multiple condition could be
Sub Splitcells()
x = 1
For Each s In selection 'Range("a2:a22")
Range(Cells(x, 2), Cells(x, 3)) = _
Split(s, Space(1), 2)
x = x + 1
Next
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

Because you want to split it in two, you may want to consider a Macro.

Sub Demo()
Dim s As String
s = "000105 Ritz Camera Center"
[B1:C1] = Split(s, Space(1), 2)
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


I have an excel spreadsheet created from an external database program.
Unfortunatly it didn't format the cells or split the text into
different columns.

An example of which is :

000105 Ritz Camera Center

This is all in one cell.

What i'm trying to do is split the cell in 2, having the Vendor Code in
one cell (000105) and the vendor name in the other.

Ideally i'd like to have a macro split the cell at the first blank
space, but i'm at a loss on how to do that. If anyone has an idea of
how i can do this i'd really appreciate the help. Been hitting my head
against a wall on this one.

James