| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IS IT POSSIBLE IN EXCEL WITHOUT WRITING CODE.
What i need is "FLIXER" Value. FLIXER value means reverse digit. i e FLIXER value of 123 will be 321 , FLIXER value of 12121212 will be 21212121 like that. Awaiting quick response. H S Shastri ================================================== ===== |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IS IT POSSIBLE IN EXCEL WITHOUT WRITING CODE.
This handles up to eight digits: I hope you can see the pattern to expand it for more....
=VALUE(LEFT(MOD(A1,10) & MOD(INT(A1/10),10) & MOD(INT(A1/100),10) & MOD(INT(A1/1000),10) & MOD(INT(A1/10000),10) & MOD(INT(A1/100000),10) & MOD(INT(A1/1000000),10) & MOD(INT(A1/10000000),10),LEN(A1))) HTH, Bernie MS Excel MVP "HARSHAWARDHAN. S .SHASTRI" wrote in message ... What i need is "FLIXER" Value. FLIXER value means reverse digit. i e FLIXER value of 123 will be 321 , FLIXER value of 12121212 will be 21212121 like that. Awaiting quick response. H S Shastri ================================================== ===== |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IS IT POSSIBLE IN EXCEL WITHOUT WRITING CODE.
Hello Shastri Ji..!!
Try following:- {=SUM(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)) *10^(ROW(INDIRECT("1:"&LEN(A1)))-1))} Thanks. -- Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India "HARSHAWARDHAN. S .SHASTRI" wrote: What i need is "FLIXER" Value. FLIXER value means reverse digit. i e FLIXER value of 123 will be 321 , FLIXER value of 12121212 will be 21212121 like that. Awaiting quick response. H S Shastri ================================================== ===== |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IS IT POSSIBLE IN EXCEL WITHOUT WRITING CODE.
With a value in A1, in B1 enter:
=IF(ISERROR(MID(A$1,LEN(A$1)+1-ROW(),1)),"",MID(A$1,LEN(A$1)+1-ROW(),1)) and copy down until blanks appear In C1, enter: =B1 In C2, enter: =C1 & B2 and copy down. Here is an example: qwerrty y y t yt r ytr r ytrr e ytrre w ytrrew q ytrrewq -- Gary''s Student - gsnu200831 "HARSHAWARDHAN. S .SHASTRI" wrote: What i need is "FLIXER" Value. FLIXER value means reverse digit. i e FLIXER value of 123 will be 321 , FLIXER value of 12121212 will be 21212121 like that. Awaiting quick response. H S Shastri ================================================== ===== |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IS IT POSSIBLE IN EXCEL WITHOUT WRITING CODE.
HARSHAWARDHAN. S .SHASTRI wrote:
What i need is "FLIXER" Value. FLIXER value means reverse digit. i e FLIXER value of 123 will be 321 , FLIXER value of 12121212 will be 21212121 like that. Awaiting quick response. H S Shastri ================================================== ===== This might work for you... http://tinyurl.com/awmk88 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IS IT POSSIBLE IN EXCEL WITHOUT WRITING CODE.
Glenn wrote:
HARSHAWARDHAN. S .SHASTRI wrote: What i need is "FLIXER" Value. FLIXER value means reverse digit. i e FLIXER value of 123 will be 321 , FLIXER value of 12121212 will be 21212121 like that. Awaiting quick response. H S Shastri ================================================== ===== This might work for you... http://tinyurl.com/awmk88 Modified to allow a 0 for the last/first digit, but resulting in text: =IF(MOD(A1,10)=0,"0","")&SUMPRODUCT(10^(LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1)))-1),MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1)+0) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IS IT POSSIBLE IN EXCEL WITHOUT WRITING CODE.
Oh - If you can have 0 as the last digit, then you need to drop the VALUE wrapper and live with a
string. HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... This handles up to eight digits: I hope you can see the pattern to expand it for more.... =VALUE(LEFT(MOD(A1,10) & MOD(INT(A1/10),10) & MOD(INT(A1/100),10) & MOD(INT(A1/1000),10) & MOD(INT(A1/10000),10) & MOD(INT(A1/100000),10) & MOD(INT(A1/1000000),10) & MOD(INT(A1/10000000),10),LEN(A1))) HTH, Bernie MS Excel MVP "HARSHAWARDHAN. S .SHASTRI" wrote in message ... What i need is "FLIXER" Value. FLIXER value means reverse digit. i e FLIXER value of 123 will be 321 , FLIXER value of 12121212 will be 21212121 like that. Awaiting quick response. H S Shastri ================================================== ===== |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IS IT POSSIBLE IN EXCEL WITHOUT WRITING CODE.
That fails when there are two or more final zero digits
HTH, Bernie MS Excel MVP "Glenn" wrote in message ... Glenn wrote: HARSHAWARDHAN. S .SHASTRI wrote: What i need is "FLIXER" Value. FLIXER value means reverse digit. i e FLIXER value of 123 will be 321 , FLIXER value of 12121212 will be 21212121 like that. Awaiting quick response. H S Shastri ================================================== ===== This might work for you... http://tinyurl.com/awmk88 Modified to allow a 0 for the last/first digit, but resulting in text: =IF(MOD(A1,10)=0,"0","")&SUMPRODUCT(10^(LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1)))-1),MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1)+0) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IS IT POSSIBLE IN EXCEL WITHOUT WRITING CODE.
Dilip Pandeji,
Bahot khub.very very good. Thanks. H S Shastri ================================================== ======= "DILipandey" wrote: Hello Shastri Ji..!! Try following:- {=SUM(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)) *10^(ROW(INDIRECT("1:"&LEN(A1)))-1))} Thanks. -- Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India "HARSHAWARDHAN. S .SHASTRI" wrote: What i need is "FLIXER" Value. FLIXER value means reverse digit. i e FLIXER value of 123 will be 321 , FLIXER value of 12121212 will be 21212121 like that. Awaiting quick response. H S Shastri ================================================== ===== |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IS IT POSSIBLE IN EXCEL WITHOUT WRITING CODE.
How about this?
=RIGHT(REPT("0",99)&SUMPRODUCT(10^(LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1)))-1), MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1)+0),LEN(A1)) Bernie Deitrick wrote: That fails when there are two or more final zero digits HTH, Bernie MS Excel MVP "Glenn" wrote in message ... Glenn wrote: HARSHAWARDHAN. S .SHASTRI wrote: What i need is "FLIXER" Value. FLIXER value means reverse digit. i e FLIXER value of 123 will be 321 , FLIXER value of 12121212 will be 21212121 like that. Awaiting quick response. H S Shastri ================================================== ===== This might work for you... http://tinyurl.com/awmk88 Modified to allow a 0 for the last/first digit, but resulting in text: =IF(MOD(A1,10)=0,"0","")&SUMPRODUCT(10^(LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1)))-1),MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1)+0) |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IS IT POSSIBLE IN EXCEL WITHOUT WRITING CODE.
Beautiful!
Bernie MS Excel MVP "Glenn" wrote in message ... How about this? =RIGHT(REPT("0",99)&SUMPRODUCT(10^(LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1)))-1), MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1)+0),LEN(A1)) Bernie Deitrick wrote: That fails when there are two or more final zero digits |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IS IT POSSIBLE IN EXCEL WITHOUT WRITING CODE.
Try his formula with numbers ending in one or more zeroes... those trailing
zeroes get dropped when FLIXER'ed. Here is a modification to the formula which preserves the trailing zeroes in the reversed number.... =TEXT(SUM(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))) ,1))*10^(ROW(INDIRECT("1:"&LEN(A1)))-1)),REPT("0",LEN(A1))) This is still an array-entered formula, so commit it using Ctrl+Shift+Enter and not just Enter by itself. Note: This formula will only work for numbers having 15 digits or less. -- Rick (MVP - Excel) "HARSHAWARDHAN. S .SHASTRI" wrote in message ... Dilip Pandeji, Bahot khub.very very good. Thanks. H S Shastri ================================================== ======= "DILipandey" wrote: Hello Shastri Ji..!! Try following:- {=SUM(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)) *10^(ROW(INDIRECT("1:"&LEN(A1)))-1))} Thanks. -- Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India "HARSHAWARDHAN. S .SHASTRI" wrote: What i need is "FLIXER" Value. FLIXER value means reverse digit. i e FLIXER value of 123 will be 321 , FLIXER value of 12121212 will be 21212121 like that. Awaiting quick response. H S Shastri ================================================== ===== |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IS IT POSSIBLE IN EXCEL WITHOUT WRITING CODE.
This variation on .. formula which I posted elsewhere in this thread is
shorter and uses 2 function calls less... =TEXT(SUM((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))* 10^(ROW(INDIRECT("1:"&LEN(A1)))-1)),REPT("0",LEN(A1))) -- Rick (MVP - Excel) "Glenn" wrote in message ... How about this? =RIGHT(REPT("0",99)&SUMPRODUCT(10^(LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1)))-1), MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1)+0),LEN(A1)) Bernie Deitrick wrote: That fails when there are two or more final zero digits HTH, Bernie MS Excel MVP "Glenn" wrote in message ... Glenn wrote: HARSHAWARDHAN. S .SHASTRI wrote: What i need is "FLIXER" Value. FLIXER value means reverse digit. i e FLIXER value of 123 will be 321 , FLIXER value of 12121212 will be 21212121 like that. Awaiting quick response. H S Shastri ================================================== ===== This might work for you... http://tinyurl.com/awmk88 Modified to allow a 0 for the last/first digit, but resulting in text: =IF(MOD(A1,10)=0,"0","")&SUMPRODUCT(10^(LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1)))-1),MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1)+0) |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IS IT POSSIBLE IN EXCEL WITHOUT WRITING CODE.
Actually, we don't need the VALUE function call in the formula...
=TEXT(SUM((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))* 10^(ROW(INDIRECT("1:"&LEN(A1)))-1)),REPT("0",LEN(A1))) -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Try his formula with numbers ending in one or more zeroes... those trailing zeroes get dropped when FLIXER'ed. Here is a modification to the formula which preserves the trailing zeroes in the reversed number.... =TEXT(SUM(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))) ,1))*10^(ROW(INDIRECT("1:"&LEN(A1)))-1)),REPT("0",LEN(A1))) This is still an array-entered formula, so commit it using Ctrl+Shift+Enter and not just Enter by itself. Note: This formula will only work for numbers having 15 digits or less. -- Rick (MVP - Excel) "HARSHAWARDHAN. S .SHASTRI" wrote in message ... Dilip Pandeji, Bahot khub.very very good. Thanks. H S Shastri ================================================== ======= "DILipandey" wrote: Hello Shastri Ji..!! Try following:- {=SUM(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)) *10^(ROW(INDIRECT("1:"&LEN(A1)))-1))} Thanks. -- Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India "HARSHAWARDHAN. S .SHASTRI" wrote: What i need is "FLIXER" Value. FLIXER value means reverse digit. i e FLIXER value of 123 will be 321 , FLIXER value of 12121212 will be 21212121 like that. Awaiting quick response. H S Shastri ================================================== ===== |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IS IT POSSIBLE IN EXCEL WITHOUT WRITING CODE.
Sorry, I forgot to paste HARSHAWARDHAN. S .SHASTRI's name in to my
message... he was the creator of the original formula which I modified. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... This variation on .. formula which I posted elsewhere in this thread is shorter and uses 2 function calls less... =TEXT(SUM((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))* 10^(ROW(INDIRECT("1:"&LEN(A1)))-1)),REPT("0",LEN(A1))) -- Rick (MVP - Excel) "Glenn" wrote in message ... How about this? =RIGHT(REPT("0",99)&SUMPRODUCT(10^(LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1)))-1), MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1)+0),LEN(A1)) Bernie Deitrick wrote: That fails when there are two or more final zero digits HTH, Bernie MS Excel MVP "Glenn" wrote in message ... Glenn wrote: HARSHAWARDHAN. S .SHASTRI wrote: What i need is "FLIXER" Value. FLIXER value means reverse digit. i e FLIXER value of 123 will be 321 , FLIXER value of 12121212 will be 21212121 like that. Awaiting quick response. H S Shastri ================================================== ===== This might work for you... http://tinyurl.com/awmk88 Modified to allow a 0 for the last/first digit, but resulting in text: =IF(MOD(A1,10)=0,"0","")&SUMPRODUCT(10^(LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1)))-1),MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1))),1)+0) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Writing a rule in excel... I think | Excel Worksheet Functions | |||
| Need help writing Code | Excel Discussion (Misc queries) | |||
| Can you call a Web Service from Excel 2007 without writing code? | Excel Discussion (Misc queries) | |||
| Writing macro in Excel | Excel Worksheet Functions | |||
| Help writing an Excel Formula | Excel Worksheet Functions | |||
![HARSHAWARDHAN. S .SHASTRI[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
