seperating text in one cell to multiple cells

[Joe]

Hi,

I would like to have a Macro to split the text entries in one cell into
two or more other cells.

eg: if the entries are like:

Cell D2 : "CX55742A-CI CY55742AAA-CI#"
Cell D3: "BY58575B-BB"
Cell D4: "95033 95982111S 95982199"
etc,

what i would liek to do is to split D2 into:

E2: "CX55742A-CI"
F2: "CY55742AAA-CI#"

I guess I should read from the left, look for spaces in the text, and
split the entry right where the space is, move to the next actual text
entry, etc. However, please note that:

1. I dont know in advance how many sub-text entries are going to be in
one cell, so I dont know how many columns I'd be splitting this into
2. The part numbers have different string lengths, so I cant use the
easier way of saying "pick the first 8 characters and put em in E2, the
next 5 in F2, etc

Do you think you could help me with this? Thanks a lot in advance. This
is partof a big project, and I've already learnt a lot from this group
- 'appreicate all the help you guys give to novices like me.

Joe.

 

[Doug Kanter]

Hi,

I would like to have a Macro to split the text entries in one cell into
two or more other cells.

eg: if the entries are like:

Cell D2 : "CX55742A-CI CY55742AAA-CI#"
Cell D3: "BY58575B-BB"
Cell D4: "95033 95982111S 95982199"
etc,

what i would liek to do is to split D2 into:

E2: "CX55742A-CI"
F2: "CY55742AAA-CI#"

I guess I should read from the left, look for spaces in the text, and
split the entry right where the space is, move to the next actual text
entry, etc. However, please note that:

1. I dont know in advance how many sub-text entries are going to be in
one cell, so I dont know how many columns I'd be splitting this into
2. The part numbers have different string lengths, so I cant use the
easier way of saying "pick the first 8 characters and put em in E2, the
next 5 in F2, etc

Do you think you could help me with this? Thanks a lot in advance. This
is partof a big project, and I've already learnt a lot from this group
- 'appreicate all the help you guys give to novices like me.

Joe.

Questions:

1) What is the source of the data? Another program? Or, is it manually
entered by humans?

2) Are those quotation marks only here in your message, or do they actually
exist in the text to need to break up?

 

[Anne Troy]

Have you tried Data-->Text to columns? Be careful that you save a copy of
your file first, and that as many columns to the right as are needed will
NOT be in use.
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

 

[Doug Kanter]

Have you tried Data-->Text to columns? Be careful that you save a copy of
your file first, and that as many columns to the right as are needed will
NOT be in use.
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

Based on the samples he provided, that wouldn't work.

 

[Guest]

In E2 put the following formula:
=LEFT(D2,FIND(" ",D2,1)-1)

In D2 put the following formula
=TRIM(RIGHT(D2,LEN(D2)-FIND(" ",D2,1)))

That should do it.

 

[Joe]

Thanks for the qns. More on those:

1. The columns A are being copied from a daily report by another
macro in the same module into this worksheet.This parent report is
generated daily by an archaic database program.

(The entries in column D have to be split so that I can do an exact
match comparison against another excel spreadsheet column) Which is
also the reason why I dont have any control over the entries, or the
order in which they occur.

2. Yes, I put the quotes in the message - there are no quotation marks
in the actual text.

Please feel free to post any more questions, if you have any. i really
appreciate your time and consideration.

Thanks,

Joe.

 

[Joe]

Wont that be a circular reference, when I put in

D2 =TRIM(RIGHT(D2,LEN(D2)-FIND(" ",D2,1))) ?

Another issue is that the columns A are being copied by another macro
in the same module into this worksheet. So I dont know if this would
work. Please advise.

thanks,

Joe.

 

[Doug Kanter]

Thanks for the qns. More on those:

1. The columns A are being copied from a daily report by another
macro in the same module into this worksheet.This parent report is
generated daily by an archaic database program.

(The entries in column D have to be split so that I can do an exact
match comparison against another excel spreadsheet column) Which is
also the reason why I dont have any control over the entries, or the
order in which they occur.

2. Yes, I put the quotes in the message - there are no quotation marks
in the actual text.

Please feel free to post any more questions, if you have any. i really
appreciate your time and consideration.

Thanks,

Joe.

Which archaic database program is producing the data? I'm asking because no
matter how archaic, many database apps allow the creation of custom reports.
You can design the reports to use certain symbols to delimit (separate) one
field (column) from another.

 

[Joe]

Thanks for the observation. However, problem is, I am trying to create
a macro for this.

Appreciate it, all the same.

Joe.

 

[Joe]

It is a company-specific business program. Our IT team controls it, so
I have absolutely no say over that. (And sadly, I have come to realize
that IT is not exactly the fastest group in the bunch)

So I'm sure it can be done, but I doubt it if they will do it even if I
put in a request. Plus, this is also adding to my expreience with VB
macros, so I shall take it that way and learn some new techniques from
you old hands, rt?.

Thanks,

Joe.

 

[Joe]

Thanks for the questions. More info on those:

1. This data in columns A is being copied into this worksheet from
another excel report (a macro int he same module does the copying). The
parent excel worksheet is generated daily by our archaic database
program.

2. The quotes are just for my message - they dont appear in the actual
text.

please feel free to ask for more info, if you have more questions. I
really appreciate your time and consideration.

Thanks,

Joe.

 

[Ron Rosenfeld]

Hi,

I would like to have a Macro to split the text entries in one cell into
two or more other cells.

eg: if the entries are like:

Cell D2 : "CX55742A-CI CY55742AAA-CI#"
Cell D3: "BY58575B-BB"
Cell D4: "95033 95982111S 95982199"
etc,

what i would liek to do is to split D2 into:

E2: "CX55742A-CI"
F2: "CY55742AAA-CI#"

I guess I should read from the left, look for spaces in the text, and
split the entry right where the space is, move to the next actual text
entry, etc. However, please note that:

1. I dont know in advance how many sub-text entries are going to be in
one cell, so I dont know how many columns I'd be splitting this into
2. The part numbers have different string lengths, so I cant use the
easier way of saying "pick the first 8 characters and put em in E2, the
next 5 in F2, etc

Do you think you could help me with this? Thanks a lot in advance. This
is partof a big project, and I've already learnt a lot from this group
- 'appreicate all the help you guys give to novices like me.

Joe.

If you have a later version of Excel with VBA6+, then:

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

Sub SplitData()
Dim t() As String
Dim c As Range
Dim i As Long

For Each c In Selection
t = Split(Replace(Application. _
WorksheetFunction.Trim(c.Text), """", ""))
For i = 0 To UBound(t)
c.Offset(0, i + 1).Value = t(i)
Next i
Next c
End Sub
===============================
--ron

 

[Doug Kanter]

Thanks for the observation. However, problem is, I am trying to create
a macro for this.

Appreciate it, all the same.

Joe.

Whether by macro or manually, the text to columns feature only works of the
data is of uniform length, which yours is not. It's a good trick to know for
the future, however, for other data you might run across.

 

[Anne Troy]

LOL, Doug. He said D2, D3, and D4, and I saw something like D2, E2, and
F2... different columns.
Sorry, Joe!
************
Hope it helps!
Anne Troy
www.OfficeArticles.com