If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
text to column
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, |
#2
|
|||
|
|||
text to column
Say we are going to put a number in A1. In B1 enter:
=MID($A1,COLUMN()-1,1) and copy across -- Gary''s Student - gsnu200818 |
#3
|
|||
|
|||
text to column
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) On Mon, 8 Dec 2008 10:10:12 -0800, ChewinFoil wrote: 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, |
#4
|
|||
|
|||
text to column
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 -)------ "Gary''s Student" wrote: Say we are going to put a number in A1. In B1 enter: =MID($A1,COLUMN()-1,1) and copy across -- Gary''s Student - gsnu200818 |
#5
|
|||
|
|||
text to column
Thanks, That solves it! and I understand why as well!!
Really appreciate the rapid response. ChewinFoil --)------ "Chip Pearson" wrote: 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) On Mon, 8 Dec 2008 10:10:12 -0800, ChewinFoil wrote: 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, |
#6
|
|||
|
|||
text to column
Thanks.
Chip Pearson got it appreciate all the help. I now understand. "Gary''s Student" wrote: Say we are going to put a number in A1. In B1 enter: =MID($A1,COLUMN()-1,1) and copy across -- Gary''s Student - gsnu200818 |
#7
|
|||
|
|||
text to column
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. -- Gary''s Student - gsnu200818 "ChewinFoil" wrote: 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 -)------ "Gary''s Student" wrote: Say we are going to put a number in A1. In B1 enter: =MID($A1,COLUMN()-1,1) and copy across -- Gary''s Student - gsnu200818 |
#8
|
|||
|
|||
text to column
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. -- ChewinFoil --)-------- "Chip Pearson" wrote: 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) On Mon, 8 Dec 2008 10:10:12 -0800, ChewinFoil wrote: 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, |
#9
|
|||
|
|||
text to column
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 On Mon, 8 Dec 2008 12:13:01 -0800, ChewinFoil wrote: 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. |
#10
|
|||
|
|||
text to column
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. -- ChewinFoil --)-------- "Gord Dibben" wrote: 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 On Mon, 8 Dec 2008 12:13:01 -0800, ChewinFoil wrote: 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. |
|
Thread Tools | |
Display Modes | |
|
|