Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas that include alpha and numeric characters
Is this even possible? I have custom data in cells that contain letters and
numbers that I need to divide by 4 to one column and multiply by 5 into another column. I need to duplicate it over 12,000 times. Here is an example: Primary cell contains 'B-3Y30' where 'B' remains constant and 'Y' equals 48. Divide cell then reads 'B-43 1/2' and the multiply cell then reads 'B-18Y6'. The divide cell converts 'Y' to 48 then times 3, adds 30 and divides by 4. The multiply cell converts 'Y' to 48 then times 3, adds 30, multiplies by 5 and changes the sum to number of "Y's" at 48 with under 48 as the actual number. Also, the 'B' varies to other letters and sometimes 2 or 3 characters (AXX or RN) and all need to remain constant. I can send an example of a few if needed. Pretty wild huh? Any help will be appreciated. -- The Doktor from Mile High |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas that include alpha and numeric characters
Is there always a "Y" in the second part? Don't know if this'll work for all
cases but if your data is in A1 =LEFT(A1,FIND("-",A1))&TEXT(MID(A1,FIND("-",A1)+1,FIND("Y",A1,FIND("-",A1)+1)-FIND("-",A1)-1)*12+REPLACE(A1,1,FIND("Y",A1,FIND("-",A1)+1),"")/4,"00 ?/?") and =LEFT(A1,FIND("-",A1))&INT(MID(A1,FIND("-",A1)+1,FIND("Y",A1,FIND("-",A1)+1)-FIND("-",A1)-1)*5+REPLACE(A1,1,FIND("Y",A1,FIND("-",A1)+1),"")*5/48)&"Y"&MOD(MID(A1,FIND("-",A1)+1,FIND("Y",A1,FIND("-",A1)+1)-FIND("-",A1)-1)*240+REPLACE(A1,1,FIND("Y",A1,FIND("-",A1)+1),"")*5,48) "Doktor1 at Mile High" wrote: Is this even possible? I have custom data in cells that contain letters and numbers that I need to divide by 4 to one column and multiply by 5 into another column. I need to duplicate it over 12,000 times. Here is an example: Primary cell contains 'B-3Y30' where 'B' remains constant and 'Y' equals 48. Divide cell then reads 'B-43 1/2' and the multiply cell then reads 'B-18Y6'. The divide cell converts 'Y' to 48 then times 3, adds 30 and divides by 4. The multiply cell converts 'Y' to 48 then times 3, adds 30, multiplies by 5 and changes the sum to number of "Y's" at 48 with under 48 as the actual number. Also, the 'B' varies to other letters and sometimes 2 or 3 characters (AXX or RN) and all need to remain constant. I can send an example of a few if needed. Pretty wild huh? Any help will be appreciated. -- The Doktor from Mile High |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change the column designation from a numeric to an alpha | Excel Discussion (Misc queries) | |||
Can you ID a cell that has both Alpha AND Numeric characters? | Excel Worksheet Functions | |||
The colums changed from alpha to numeric how do you make it alpha | Excel Discussion (Misc queries) | |||
Alpha & Numeric Counts in Excel | Excel Discussion (Misc queries) | |||
Count unique alpha numeric "characters" in a common cell | Excel Worksheet Functions |