Seperate Data in Excel

[DanaPach]

Hello,

I need help seperating data within a cell in Excel.
I need to seperate the Code and the description.
I would like the Code to be in one cell and the description to be another cell.
For example:
2095ManufacturingCOFFEE EXTRACTS
is combined in one cell.
I need 2095Manufacturing to be in one cell
and COFFEE EXTRACTS to be in the next cell
The descriptions are UCase.
I have over 22,000 records to seperate and need a quick way to do this.

I would appricate any help either by a macro or in VB.

Thank you,

Dana

 

[SkipVought]

Dana,

Paste this procedure into a module. Select the data range and run.

Sub ParseOnUCase()
   For Each s In Selection
      With s
         For i = Len(.Value) To 1 Step -1
            If .Characters(i, 1).Text <> UCase(.Characters(i, 1).Text) Then
               
               Exit For
            End If
         Next
         .Offset(0, 1).Value = .Characters(i + 1, Len(.Value)).Text
         .Value = .Characters(1, i).Text
      End With
   Next
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[DanaPach]

Skip,

Thank you for your help. I appricate your response.

Dana