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


---
Message posted from http://www.ExcelForum.com/

datatext to columns

--
Don Guillett
SalesAid Software

"Jmbostock" wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

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" wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

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 *

"Jmbostock" wrote
in message
...
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


---
Message posted from http://www.ExcelForum.com/



.

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

Thanks


---
Message posted from http://www.ExcelForum.com/

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" wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

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

"Dana DeLouis" wrote in message
...
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" wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

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" wrote in message
...
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

"Dana DeLouis" wrote in message
...
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" wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/