How to split text?

  • Thread starter Thread starter Jmbostock
  • Start date Start date
J

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
 
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 *
 
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 *




.
Click to expand...
 
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
 
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)
Dana DeLouis said:
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
Click to expand...
 
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


Don Guillett said:
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)
Dana DeLouis said:
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
= = = = = = = = = = = = = = = = =


Jmbostock said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

Tom Ogilvy said:
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


Don Guillett said:
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)
Dana DeLouis said:
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
Click to expand...
Click to expand...
Click to expand...
 
OK. I like em short.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Tom Ogilvy said:
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


Don Guillett said:
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)
Dana DeLouis said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top