text to column

[ChewinFoil]

I am copying data from a web app into excel. As series of numbers copies in
as one field. Example: 5 4 4 5 6 5 5 as 5445655. I know how to use text to
column and a width as delimiter to break this back up into individual numbers
but I don't want to have to run the wizard every time. Is there any way to
make a number pasted into a cell automatically break down into it's component
digits?

Thanks,

 

[Gary''s Student]

Say we are going to put a number in A1. In B1 enter:

=MID($A1,COLUMN()-1,1) and copy across

 

[Chip Pearson]

You can do it with code:

Sub AAA()
Dim InputText As String
Dim Dest As Range
Dim N As Long

' InputText is the text to be split.
' Change to the appropriate cell.
InputText = Range("A1").Text
' OR
'InputText = ActiveCell.Text

' Dest is the location to write the
' digits of InputText. Change as needed.
Set Dest = Range("B1") ' start output cell
' OR
'Set Dest = ActiveCell(1, 2)
For N = 1 To Len(InputText)
Dest(1, N).Value = Mid(InputText, N, 1)
Next N
End Sub

Or, you can use a formula. If the data to be split is in cell A1, and
the individual digit cells begin in F3, use

=MID($A$1,COLUMN()-COLUMN($F$3)+1,1)

Then, copy this formula to the right for (at least) as many columns as
there are characters in A1.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[ChewinFoil]

Thanks. I'm really new to functions and trying to understand what they all
do. I really appreciate the rapid reply. The data that I paste in is
actually in three cells H7, I7, J7. H7 winds up being a 7 digit number and I
want the resulting digits to go to V18:AB18. I7 is 8 digits and I want it to
go to AE18:AL18. J7 is 8digits and I want it to go to N18:U18. I tried
replacing the A1 in the function you provided with the appropriate cell and
copying it into where I wanted the data to wind up but it didn't work.
Obviously I don't really understand the function. Any help would appreciated
and if you could briefly explain why, that's all the better. I like thing to
work but I like to know why as well.
Again thanks for responding so quickly.

ChewinFoil -)------

 

[ChewinFoil]

Thanks, That solves it! and I understand why as well!!
Really appreciate the rapid response.

ChewinFoil --)------

 

[ChewinFoil]

Thanks.
Chip Pearson got it appreciate all the help. I now understand.

 

[Gary''s Student]

For data in H7, in some other cell enter:

=MID($H$7,COLUMNS($A:A),1) and copy across

This splits H7 apart, character-by-character.


My previous post would only work for B2.

 

[ChewinFoil]

So now that I understand the MID function is there any way to use the
individually displayed characters as numbers. Since the function is just
displaying the character in that position is there any way to use the actual
value? For example from my original question could the 5 4 4 5 6 5 5 now be
added to equal 34. Thanks.

 

[Gord Dibben]

Chip's code returns each digit to a separate cell as a number.

Simply SUM the range.

Or..........if you want to leave all the digits in one cell like 5445655

Use this formula =SUMPRODUCT(--MID($A$1,ROW(INDIRECT("1:" & LEN($A$1))),1))

which returns 34


Gord Dibben MS Excel MVP

 

[ChewinFoil]

I already tried SUM on range but result is zero. But I was using the MID
function solely, I'll try Chip's code instead, thanks.

 

[Gord Dibben]

The MID function is a text function and will leave the "numbers" as text.

I don't know what MID formula you used, but you could probably add a *1
which forces the text to numeric.

e.g. =MID(H3,3,2)*1


Gord