• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need a formula

akinkaraman

akinkaraman

Member
I have to tables : A1 and A2

I have to get the A2 table values automaticly (like in the attached file) due to A1 table values. Can you help me?
 

Attachments

Hi ,

Is this acceptable ?

=SUBSTITUTE(MID(TEXT($S3,"000.0"),COLUMN()-COLUMN($S3)+1,1),".",",")

Enter this in S9 , and copy across and down.

Narayan
 
Sir could you please put the excel file. Because of I use Turkish Excel it doesn't work; I tried to write turkish formula but it gives error at $S3,"000.0" now.
 

Hi !

Tip : how to enter a native (english) Excel formula in a local version ?

• Select the formula destination cell in a worksheet.
• Open VBE (Alt F11).
• Open Immediate window (Ctrl G).
• Type the formula in this window : activecell.formula="formula" & validate.

You like this ? So thanks to click on bottom right Like !
​
 
Hi ,

I changed my Windows region setting to Turkey , and the following formula works :

=SUBSTITUTE(MID(TEXT($S3;"000,0");COLUMN()-COLUMN($S3)+1;1);".";",")

Narayan
 
Hello Marc..

How will I validate it?

And will I write to formula ın that box such as like :

=SUBSTITUTE(MID(TEXT($S3;"000,0");COLUMN()-COLUMN($S3)+1;1);".";",")

or like with - activecell.formula='' - :

activecell.formula="=SUBSTITUTE(MID(TEXT($S3;"000,0");COLUMN()-COLUMN($S3)+1;1);".";",")"

Thanks for tip :)
 
Hi ,

I have no idea of what the right values are ; all I can see is that when I change my Windows region setting from English (India) to Turkish (Turkey) , the formula , which was :

=SUBSTITUTE(MID(TEXT($S3,"000,0"),COLUMN()-COLUMN($S3)+1,1),".",",")

changes to :

=SUBSTITUTE(MID(TEXT($S3;"000,0");COLUMN()-COLUMN($S3)+1;1);".";",")

The displayed values , which were :

0 , 0 8 9
0 , 0 1 8
0 , 1 0 7

change to :

0 8 9 , 1
0 1 8 , 0
1 0 7 , 1

Narayan
 
akinkaraman said:
How will I validate it?
Click to expand...
validate just means hit Enter key …


Narayan, local Excel version no matter with Windows region setting
but with local Excel formula language !

For example english inner formula =COUNTIF(B1:B9,1) (.Formula)
is in my french local version =NB.SI(B1:B4;1) (.FormulaLocal) …
​
 
Hi Marc ,

It is obvious I cannot try the Turkish version ; I am merely trying to help by stating what I have tried ; if that does not help , there is really nothing more I can do ; if you can please do so.

Narayan
 
By the way I found a formula which works now.

For S9 :

=IF(COLUMNS(T9:$X9)-2<0;10*MOD($S3;1);IF(COLUMNS(T9:$X9)-2=0;",";IF(COLUMNS(T9:$X9)-2> LEN(INT($S3));"";0+ LEFT(RIGHT(INT($S3); COLUMNS(T9:$X9)-2);1))))


(I tried to write in English format.)
 


Narayan, yet done in post #9 :
tip to enter an english inner formula in a local Excel version …

But seems no one Like it Edit : Thanks akinkaraman ! :)​
 
Last edited:
Hi Marc ,

I am still not sure what the problem at OP's end is !

The formula I posted initially separates the digits in my version of Excel , which is the English version , in my regional setting , which is English (India).

When I change my Windows regional setting to Turkish (Turkey) , the characters change accordingly , the parameter separator changing from the comma to the semicolon , and the decimal character from the period to the comma.

Thus , the formula no longer works correctly , since the formula itself is not changed ; the format string to the TEXT function remains 000.0 unchanged. That is why I suggested that this could be changed to 000,0 to match the regional settings at OP's end. With this change , the formula works at my end with Turkish settings for the region. Obviously with this change , the formula will not work in my normal English settings.

Now , if OP confirms that the formula still does not work at his end , then something else is at play , either the more advanced settings in Windows ( I did not change my system locale settings since they required a restart ) , or the Turkish version of Excel , which I don't have.

The e90e50 site has this , which may help :

https://sites.google.com/site/e90e50/vba/application-international-settings

Narayan
 

Yes you're obviously right for decimal and thousand separators.

I just pointed out the Excel local formula language.
For example, your post #5 formula is in my local version :​

=SUBSTITUE(STXT(TEXTE($S3;"000.0");COLONNE()-COLONNE($S3)+1;1);".";",")


I forgot to mention in the tip to double the double quotes in the formula :​

activecell.formula="=SUBSTITUTE(MID(TEXT($S3,""000.0""),COLUMN()-COLUMN($S3)+1,1),""."","","")"
 
Last edited:
You must log in or register to reply here.
Back
Top