Does not work for me
DougStroud wrote on May 24, 2006 09:28 EST |
I am only able to extract one digit from the number.
Hmmmm? |
janet21 wrote on May 24, 2006 10:02 EST |
| Had the same problem. Just copy with a little trial and error found you copy the formula into the relevant cells and it works |
Why "-1" in Formula?
cccgsmith wrote on May 24, 2006 10:15 EST |
| It worked, but I had to delete the "-1" in the formula for the first digit to appear properly in the same column as the original number. |
Why would I use this?
rasmaniac wrote on May 24, 2006 10:30 EST |
| I can't think of any use for this tip. Any ideas? |
This is cool
Rajiv wrote on December 31, 1969 19:00 EST |
| Aha this sounds cool. but would it not be great to have tips with possible applications? I am thinking hard, where to use this tip? |
I wish I had known
MATECH wrote on December 31, 1969 19:00 EST |
| I wish I had known this. I'm converting data for our new HR and Finance Software and some of the data needed to be separated based on the old method of storage. |
This is cool
Rajiv wrote on December 31, 1969 19:00 EST |
| Aha this sounds cool. but would it not be great to have tips with possible applications? I am thinking hard, where to use this tip? |
USAGE
atesumpu wrote on December 31, 1969 19:00 EST |
| One could modify this formula for parsing .DBF or .CSV files. |
janet21 wrote on May 24, 2006 11:39 EST |
| I suppose that if someone has a lot of single figures across the columns at least it would only be one column to check back! other than that not sure. |
Conor wrote on May 24, 2006 15:28 EST |
This tip only works if your text is in column A. Otherwise you need to revise the formula to indicate the column your text is actually in.
The "column()-1" section says take the column number the formula is in (Column B = 2; Column C = 3, etc.), subtract 1 from that number and give me that character position in the text string.
So, if 9876 is in column A, the formula in column B will give the first character or "9"; the formula in column C will give the second character or "8", etc.:) |
Good
kannan wrote on December 31, 1969 19:00 EST |
| This is really good.But if you want to use it in different column this will not work.Correct?.Because column() function will give the value of correct column only. |
Usage
Ashish wrote on December 31, 1969 19:00 EST |
Am not able to figure out where to use it?
May be if we have to design a Digit to word converter it may be useful. |
Usage
Ashish wrote on December 31, 1969 19:00 EST |
Am not able to figure out where to use it?
May be if we have to design a Digit to word converter it may be useful. |
There is use
GReeN78 wrote on December 31, 1969 19:00 EST |
| Hi, I'm working in a bank and we have to make electronic payment orders, where every digit has to be in separate cell but it has to be written in one cell to be more comfortable for the user. This method is good, but not enough according to me, because if you have an amount, lets say EUR 265.40 it will not pick up the last digit which is "zero" and Excel skips it. In column "G" with this formula you will get "" instead of 0. In order to avoid this i'm using this with combination with IF ... ROUNDDOWN .. is equal to A2 in order to extract the zero at the end and populate it in the proper cell. |
Separating a number into digits
Ghassan Mhanna wrote on December 31, 1969 19:00 EST |
it doesn't work, the formula should be as folows:
=MID($A2,COLUMN()-COLUMN($A2),1)
And now it can be draged in the right direction to other cells
|
Extrapolating numbers into single digits per columm
Sasha Frugone wrote on July 06, 2006 17:00 EST |
| I use a similar function in a macro that converts dollars & cents into words |
Separating Numbers Into Digits per column or rows
Sfrugone wrote on December 31, 1969 19:00 EST |
| What I use this type of function for is to convert dollars into written words. |
Correct usage
Sankara Courtallam R wrote on December 31, 1969 19:00 EST |
The formula worked with slight change,
"=mid($A2,column(),1)"
|
Close but no cigar
ken@kiacomm.us wrote on December 31, 1969 19:00 EST |
I have need for this type of formula, but I have the following combination of letters and numbers to deal with.
HBFO(156)(156)(48)(36)[6]. I would like to extract the numbers between the "( and ")"; "[" and "]", and separate them into columns. like this:
A B C D E F G
156 156 48 36 5
Your help would be greatly appreciated
-ken |
ado1963 wrote on August 18, 2006 04:45 EST |
[I]Ken Wrote:
I have need for this type of formula, but I have the following combination of letters and numbers to deal with.
HBFO(156)(156)(48)(36)[6]. I would like to extract the numbers between the "( and ")"; "[" and "]", and separate them into columns. like this:
A B C D E F G
156 156 48 36 5 [/I]
If the format of the string is constant, ie always 4 letters, then 3 bracketed numbers, a further 3 bracketed numbers etc., the Text to columns wizard with fixed with on the data menu could prove useful |
kbeard wrote on August 21, 2006 08:31 EST |
[QUOTE=ken@kiacomm.us]I have need for this type of formula, but I have the following combination of letters and numbers to deal with.
HBFO(156)(156)(48)(36)[6]. I would like to extract the numbers between the "( and ")"; "[" and "]", and separate them into columns. like this:
A B C D E F G
156 156 48 36 5
Your help would be greatly appreciated
-ken[/QUOTE]
Ken,
This may be what you are looking for. Or at least it will point you in the right direction. You can use numbers or letters in this type Syntax. You can find help on this matter by pressing F1 in Excel. |
sounds good
manoj wrote on December 31, 1969 19:00 EST |
It worked, but I am not getting the first digit to appear properly in the same column as the original number. i.e.
if i have to seperate 243256 using formula
first digit is missing & result in 4 3 2 5 6
missing 2
??????????
|
It doesn't work with very large numbers
Ilikepi wrote on May 18, 2007 22:42 EST |
| The program is very useful to me, but unfortunatly I have incountered a problem (I understand there are many people have found but this is the only one I have incountered) it doesn't wok with very large numbers. like if you entered a very large number, say a pi to 100 decimal points, instead of putting the large number in the cell it puts a rounded scientific notation, the program mirrors what's in the cell not the text bar. So if you were to do this you would get a streched out version of sciefific notation, not the number. I don't know ow to fix this and it would be greatly appreciated if someone could help fix the problem (if it's fixable). |
Separating a Number Into Digits
myBoo wrote on July 20, 2007 12:01 EST |
It seems that MID() and COLUMN() can give varying results.
Example: Cell A2 = 1234567890
If cell A5 contains =Mid($A2,Column(),1) .... it will result in 1
If cell B5 contains =Mid($A2,Column(),1) .... it will result in 2
But,
If cell B5 contains =Mid($A2,Column(A2),1) .... it will result in 1
If cell B5 contains =Mid($A2,Column(A2),2) .... it will result in 12
=Column(), by itself, returns the reference to the column the formula is entered in. So, if B5 contains =Mid($A2,Column()-1,1) ... it will result in 1 because Column B is the 2nd column (2-1) = 1.
=Column(A2) would result in 1 as you are referencing cell A2 and column A is the 1st column.
This tells me the originator of the Tip did not need to include Column()-1,1 in their formula.... Column(),1 would work.
You can test this out by entering =Column()-1 in Cell F2. Column F is the sixth column in a spreadsheet. Your results will be 5.
I tested all of the above on Excel 2000. Results may vary on other versions.
To make this even more confusing...
If cell B5 contains =Mid($A2,Column(A2)*4,2) ... it will result in 45. The *4 tells Excel which digit to start with in the cell A2 and the ,2 tells Excel to return 2 digits.
Bottom Line: You need to understand =Mid() and =Column() in order to make this work correctly. |
Microsoft Excel 2002 Simply Visual
