Code For Text To Columns

[APElliott]

Hello,

I'm trying to automate a Text to Column routine, but can quiet manage.

The trouble is as follows.

The column in question is B and generally the text is a number followed by a letter ie 1A, 2A, 10A, and I want the number to remain in B and the letter to go to C.

If I do it manually it take a couple of goes.

Thanks

Andrew

 

[SkipVought]

Drew,

If it is ALWAYS some numeric string followed by A SINGLE ALPHA CHARACTER, then assuming that the column is A

the numeric part in col b...

=left(A1,len(A1)-1)

the alpha part in col c...

=right(A1,1)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

or if you want to write code, select the range...

for each c in selection
  with c
    .value = left(.value, len(.value) -1)
    .offset(0, 1).value = right(.value, 1)
  end with
next

The spreadsheet process makes 2 new columns of data.

the VBA makes 1 new column of data.




Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[APElliott]

Hi Skip,

I've tried running it on col G as below, but it saying invalid procedure?

lRow = Cells(65536, 1).End(xlUp).Row
Range("g2:g" & lRow).Select
For Each c In Selection
With c
.Value = Left(.Value, Len(.Value) - 1)
.Offset(0, 1).Value = Right(.Value, 1)
End With
Next


Thanks

Andrew

 

[SkipVought]

try changing this...

lRow = Cells(65536, "G").End(xlUp).Row
Cells(lRow, "G")Select
...

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[APElliott]

Hi Skip,

I've ran the code, but its losing the alpha part.

Also I had to fill in a few blank cells, how would I get it to go past blank cells,

Cheers,

Andrew

 

[SkipVought]

Post a sample of your source data

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com