splitting amount into individual cells starting from the right

[ice673]

this is what i'm trying to do

sample.xlsx

i tried using MID function but it starts from the left and it returns blank when the number is zero in the decimal places.

what other functions can i try?

[JieJenn]

Try format your number as general and use text to columns

[ice673]

hi jiejenn

i also did that but it also starts from the left.

here is the comparison

Copy of Copy of sample-2.xlsx

[tkowal]

Please see attached file...

I believe I accomplished what you wanted (Without VBA code)... It is messy but works... You can only copy formulas down not across. The sample is only valid for 12 digit numbers (when stripped of the "," and ".")

enjoy,

Ted

[mahju]

Hi
Please see attaached

Yellow formula --- copy down

Green Can copy across as well as down

Can go to any number

REgards

[Haseeb Avarakkan]

Hello ice673, try this.

A1,

=IFERROR(MID(SUBSTITUTE(TEXT($N1,"0.00"),".",""),LEN(TEXT($N1,"0.00"))-(COLUMNS(A1:$L1)),1)+0,"")

Then copy across & down.

[daddylonglegs]

I used this formula in A1, copied across and down

=TRIM(LEFT(RIGHT(" "&TEXT($N1*100,0),COLUMNS(A1:$L1))))

[ice673]

hi guys thanks for your time! all your methods work!
i will try to study/understand your works so I can apply them to my purpose perfectly.
thanks for the starting point guys!