Splitting an Excel column into 2 columns

[Ben1637]

Hi Everybody,

I use Microsoft Excel 2000 and I'm pretty sure what I'm trying to do is possible, but I can't seem to figure out how to do it.

I received a bunch of data from a survey that had some open ended questions in a text file. I used Excel to open it and was able to put most of the data into fixed width columns. However, I ran into a snag. In column B - see below - the amount of digits preceding the letter "Q" varies, so I was not able to split this out into 2 columns. What I would like to do is somehow create new column B and new column C- which is comprised of column B- the number before the "Q" in new column B, and the "Q" and number after the "Q" in new column C.

For Example:

Column B
59Q7A
255Q66OTH
271Q67OTH
277Q72
302Q78A

new column B new column C
59 Q7A
255 Q660TH
271 Q67OTH
277 Q72
302 Q78A

Is this possible? I was thinking there must be a way to have Excel recognize the "Q" and put any information in column b that is after the "Q" into a new column? Any help and/or suggestions would be greatly appreciated.

Many thanks, in advance, for your help!

 

[AnotherHiggins]

In column B:
[COLOR=blue white]=LEFT(A1,FIND("Q",A1)-1)[/color]
In column C:
[COLOR=blue white]=RIGHT(A1,LEN(A1)-FIND("Q",A1)+1)[/color]

Copy the formulas down as far as needed.

If desired, you can then copy, paste special > values on columns B&C to replace the formulas with the actual text strings.

[tt]_____
-John
[/tt]Help us help you. Please read FAQ181-2886 before posting.

 

[pbrodsky]

You can't change existing cell without VBA, but, if you add 2 new columns you can do it.

in c1 put =left(b1(find("Q",b1))
in d1 put =mid(b1,find("Q",b1)+1,99)

copy down

(I didn't test all of this - might be some typos but it's approx correct)

 

[AlaskanDad]

Here ya go:

Column B	Column C			Column D
59Q7A		=LEFT(B1,FIND("Q",B1,1)-1)	=MID(B1,FIND("Q",B1,1),100)
255Q66OTH	255				Q66OTH
271Q67OTH	271				Q67OTH
277Q72		277				Q72
302Q78A		302				Q78A

Of course, you'll then have to do a copy...paste special, values to get the new columns to keep their values.

There are other ways, but this seemed pretty quick.

- Rob

 

[longhair]

Ben1637,

Or you can use vba -
Sub splitcell()
Dim w As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer
y = 0
Do While y < 790
y = y + 1
bstring = ActiveCell.Value
cstring = bstring
x = Len(bstring)
For z = x To 1 Step -1
If Mid(bstring, z, 1) = "Q" Then
bstring = Left(cstring, (z - 1))
astring = Mid(cstring, z, ((x - z) + 1))
ActiveCell.Value = bstring
ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate
ActiveCell.Value = astring
ActiveCell.Offset(rowoffset:=0, columnoffset:=-1).Activate
z = z - 1
End If
Next z
ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
Loop
End Sub

hth
regards,
longhair

 

[Ben1637]

This is great! Thank you very much...it's working perfectly.