Text function multinational format

Hi

In one of my spreadsheets I used a text function to retrieve year from a 
cell. It's important to point put that I use Polish version of Excel. The 
function (in my version) was as follows '=tekst(A1;"rrrr")' - value of A1 is 
"2009-01-23". The format "rrrr" is an equivalent of "yyyy". I than had to 
send it to someone with an English version of Excel. Unfortunately, the 
displayed value was "rrrr" and not "2009". Is it possible to use this 
function in both versions of Excel?

Kind regards
IgorM 

0
IgorM
11/25/2009 8:09:44 PM
excel 39879 articles. 2 followers. Follow

3 Replies
1003 Views

Similar Articles

[PageSpeed] 57

Ron de Bruin has lots of info about international issues here:
http://www.rondebruin.nl/international.htm

But in this case, I'd use:
=year(a1)
(this returns a number)

or
=""&year(a1)
(this returns text)

(I don't know what =year() is in Polish.)

IgorM wrote:
> 
> Hi
> 
> In one of my spreadsheets I used a text function to retrieve year from a
> cell. It's important to point put that I use Polish version of Excel. The
> function (in my version) was as follows '=tekst(A1;"rrrr")' - value of A1 is
> "2009-01-23". The format "rrrr" is an equivalent of "yyyy". I than had to
> send it to someone with an English version of Excel. Unfortunately, the
> displayed value was "rrrr" and not "2009". Is it possible to use this
> function in both versions of Excel?
> 
> Kind regards
> IgorM

-- 

Dave Peterson
0
Dave
11/25/2009 8:35:40 PM
Try to find the Polish translation for GET.WORKSPACE. In German it is 
ARBEITSBEREICH.ZUORDNEN, in Swedish H�MTA.ARBETSYTA

Define a name (like YearChar) that refers to this formula:

=INDEX(GET.WORKSPACE(37),19)

If you now have a formula in a workbook like =YearChar it will give you the 
local character for year.
Then you can use =TEXT(A1,REPT(YearChar,4))

Similar functions exist for most (if not all) international literals. 
Download Macrofun.hlp from microsoft.

Also look at Ron de Bruin's site about international issues:

http://www.rondebruin.nl/international.htm


-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"IgorM" <igorm@live.com> wrote in message 
news:20B9C926-E74F-4E4C-94E4-E249F848413E@microsoft.com...
> Hi
>
> In one of my spreadsheets I used a text function to retrieve year from a 
> cell. It's important to point put that I use Polish version of Excel. The 
> function (in my version) was as follows '=tekst(A1;"rrrr")' - value of A1 
> is "2009-01-23". The format "rrrr" is an equivalent of "yyyy". I than had 
> to send it to someone with an English version of Excel. Unfortunately, the 
> displayed value was "rrrr" and not "2009". Is it possible to use this 
> function in both versions of Excel?
>
> Kind regards
> IgorM 

0
Niek
11/25/2009 8:45:36 PM
http://www.applewood.nl/forms/3000/frm-004/?frmtaal=PL
0
huubke (6)
9/6/2011 1:49:39 PM
Reply:

Similar Artilces:

flip a sheet format
Hi, I am looking for a solution where I could change the table in a different format. Any office product solution, Excel or Access or any other tool could be used where the process can be automated (macro, SQL query, etc) I have this table: T1 App1, App2, App3 T2 App4, App5 T3 App6, App7, App8 I want to create this table of the previous table: App1 T1 App2 T1 App3 T1 App4 T2 App5 T2 App6 T3 App7 T3 App8 T3 Thank you for any help Hi The following code should do what you want. Sub ChangeLayout() Dim i As Long, j As Long, k As Long, lr As Long, lc As Long Dim wss As...

Excel 2007 How To Add New Number Formats to Styles
The Styles Functional Group in the Home Tab contains the following Style choices Custom Good Bad Neutral Data Model Titles and Headings Number Format When I created a new style it went into the Custom category but I'd like to move it to the Number Format choice. How can I add a new Style to a specific section of the Style functional group? Thanks for reading and for any help you provide. Regards (8^>)-]=[ ...

Can't get dates to format corrrectly.
Hi, I ran a MS-Access query to pull in some data from our database and one of the date fields has the data formatted as either MM/DD/YYYY or YYYY-MM-DD. I want to change the data on the Excel sheet so that all rows display this date as MM/DD/YYYY. I tried to obvious of selecting the entire column and setting the format to just that date format but still those dates remain formatted as YYYY-MM-DD. When the data came into Excel it was set to a general format for that particular column. Any ideas on how I can accomplish this? You might try this: go to any blank cell off to the side an...

Using the sum function in a 3 dimension environment
How to Use the sum function in a 3 dimension environment in Microsoft Excel? Need a detailed description for a research paper I am doing, ASAP!!!! Is this what you need? =SUM(Sheet1:Sheet3!C1) If sheet names have spaces use =SUM('sheet x:sheet y'!C1) -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Paul Hargreaves" <Paul Hargreaves@discussions.microsoft.com> wrote in message news:24F4CEEA-3A12-4922-B72B-416A9584EDD6@microsoft.com... > How to Use the sum function in a 3 dimension environment in Microsoft > Excel? > Need a detailed d...

Multiple-Test Conditional Formatting
Hi, everyone! I have a spreadsheet in which one column (B) contains an item "type", e.g. "C" or "H". In another column (G), I put a "required" sub-item. I am trying to conditionally format the cell in column G to do the following: If Col B = "C" and Col G is blank, make background of Col G red; Otherwise, make background of Col G normal. Using normal CF, I can make G's background red if Col B = "C" (formula is), or I can make it red if Col G is blank (cell value is), but can't seem to find the trick of combinin...

Access 2007: Report>OnNoData Function
In access 2000, I was able to use "docmd.cancel" after a message box in the OnNoData function to get a message box to display, but otherwise return to the calling form. In Access 2007, however, this displays the message box, and then gives me an error message on top of it. I've also tried "Cancel=True". I'm new to this version of Access. Has something Changed, here? Thanks in Advance bh Using the CancelEvent action in a macro, or setting the Cancel argument to True in code - both approaches should work in A2007. -- Allen Browne - Microsoft MVP. Pert...

macro to find date format in a cell and delete that entire row
macro to find date format in a cell and delete that entire row i have dates in a column and date keeps changing, i want that if macro find date in a column , it deletes that entire row help me thank u so much everyone for ue hel -- Message posted from http://www.ExcelForum.com try Sub deletedate() For Each c In Selection If IsDate(c) Then c.EntireRow.Delete Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "vikram >" <<vikram.15ix9t@excelforum-nospam.com> wrote in message news:vikram.15ix9t@excelforum-nospam.com... > macro to find date format in a ...

Date Function
I have two different users, who have the same machines and the same version of office. However when one of the users types in a date; i.e - 10/02/2005, and then goes to format the cell, they can click on a date type that will look like; "*Thursday 10 February 2005". However on the second users machine this function is not present, all they have the option of is; "*10 February 2005". I dont know why it is missing of the day?? Can someone please help me? -- andyb7901 ------------------------------------------------------------------------ andyb7901's Profile: http:/...

Prevent format change caused by pasting. (office '97)
Every time I paste something that was from an website (even if only one word) the column extends as far right as it allows. I found out that I can change the format (under paste special) back to text to fix this. Is there a way to prevent excel for changing format? I want it to default as text format and not change format unless I change it myself. Also is there a way to keep the text at one font and one text size, so that if I paste something that had a different size or font it would change to the size and font that I have set for my document (I want to know how to do this in Word as well.)...

Output field as a text
Hello, I am new to access 2007, and need to do the following I have created a form and in there is a selection of check boxes. Simply put for gender if they are male check here felame check here trans-m here etc. I am trying to generate a report that lists by the id number as a text string, but I am getting out the option field output number 1, 2, 3, 4 etc How do I get this to be the text value, not the field output number Please keep it simple as I am new!! thanks It sounds like your check boxes are options in an option group bound to a numeric column in the form's underlying ...

Text Box showing up on all pages
Publisher 2007 - I am trying to add a text box to one page, but it shows up on all pages. Never had this happen before. Did I set something wrong somewhere? When you inserted the pages did you click the *Create one text box on each page*? -- Mary Sauer http://msauer.mvps.org/ "Text Box showing up on all pages" <Text Box showing up on all pages@discussions.microsoft.com> wrote in message news:3511CD3C-E5A2-4D85-BCB3-325C1212CEFB@microsoft.com... > Publisher 2007 - I am trying to add a text box to one page, but it shows up > on all pages. Never had this happen befo...

Flashing Text
How can I make a text flash RED when say a cell is negative? :confused: j ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ I don't think you can, and why ever would you want to? It would drive people nuts, and is not a good design principle. You could make the background or foreground red by using Conditional Formatting. You will find it under the Format menu. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from t...

COUNTIF function with date range
Is there anyway to use the COUNTIF function with a date range. I'm trying to count each date within a particular month. For example, =COUNTIF(G5:G100, "12/**/03" And this example with the wildcards is currently not working. Thanks. Amy Hi, Try, =SUMPRODUCT(--(MONTH(G5:G100)=12)) Hope this helps! In article <fd8501c43e96$22a9daf0$a401280a@phx.gbl>, "Amy" <anonymous@discussions.microsoft.com> wrote: > Is there anyway to use the COUNTIF function with a date > range. I'm trying to count each date within a particular > month. For ...

Strike through text in Publisher 2003
I'm new to this product- how do I get strike through text? I've tried importing a style from Word but the formatting gets stripped out. Any help appreciated to meet a deadline! Microsoft decided you no longer needed Strike-through and removed it. Who do you think you are questing the decisions Microsoft make in your interest, and secondly, do you honestly really expect software programs from the same company to be compatible - unbelievable if you do and also believe in quality control. -- MSN supports racism and discrimination against indigenous Australians and Chinese. "I...

Make text boxes the same height
I have 3 seperate text boxes that are side by side on a report, they vary in height and can grow, I want to put a solid box around each but I want them all to default to the tallest so as they look uniform when printed. I have spent a long time looking at other posts and trying some OnPrint event procedures but with no luck so far. I hope someone can help, thanks in advance Open your report in Design View and right click on your textbox and open the properties. Under the Format tab you will see a Width and Height field. These are the fields to adjust your textbox. If you hold down the ...

Email
I use Word as my email format. How do I display the horizontal ruler on a new email message I'm trying to send? ...

Keeping focus on a text box
I have a form on which I'm using my own record selector buttons to move between records. I'd like to use some code to keep focus on a particular text box (ItemNum) when moving between records if ItemNum had the focus prior to moving to the next record. ItemNum is not the default 1st Text box on the form. Any suggestions? Thanks Kevin D "Kevin D." <KevinD@discussions.microsoft.com> wrote in message news:BF2CEF17-ADA7-4253-8CDD-DB2AC3DD5117@microsoft.com... > I have a form on which I'm using my own record selector buttons to move > between records. I...

how do I input text into excel worksheet cell?
All I want to do is type text into an excel worksheet cell. How do I do this so that ithe line of text stays within the cell and does not continue into the columns to the right? It can't be that complicated! Try this: From the Excel main menu: <format><cells><alignment tab> Check: Wrap text Does that help? *********** Regards, Ron XL2002, WinXP "KVJ" wrote: > All I want to do is type text into an excel worksheet cell. How do I do this > so that ithe line of text stays within the cell and does not continue into > the columns to the right? It...

insert blank line when text changes
Hi, I was wondering if there was a way to write a macro or something else... so while scrolling down a list everytime the text changes (in this case a name) a blank line would be inserted. Hi try the following macro. It tests column A and inserts a blank row if the values change Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "A").End(xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index, "A").Value <> Cells(row_index + 1, "A").Value Then Cells(row_index + 1, "A").Ent...

Convert text to formula
Hi, I have a formula such as "=sum(Hello!A1:B2)", where "Hello" is spreadsheet. I have a column with all the names of my spredsheets and need to use the same set of formula referencing to all of them, one b one. I need to, either: Replace the name of the preadsheet by a cell reference so I can use th same fomula to call various spreadsheet. or find a way to convert a text into a formula - to do the above, I ca use the "SUBSTITUTE" function into my formula but this is then a tex and I cannot find the way to evaluate it (The INDIRECT function doe not work...) ...

Extract text content from MS office files
Hi, I want to extract text content MS-OFFICE documents like word, ppt, xls... Plz gimme some tips. Regard Jack ...

INDEX-MATCH with cell text driven names
Hi, I've read through a few posts that are close to what I'm trying to do but i can't get it to work. I'm constructing a fantasy F1 spreadsheet while i'm bored off work sick. Sheetnames in workbook DriverSummary, AUS, MAL, CHN, BHR,.....for each round/country of the season. Each sheet race sheet has a column for car number in called AUSarray, MALarray etc. AUSREF is cell A1 on the race sheets My current formula in the diver summary works well and is shown below. I have N() to return 0 when a race hasn't happened yet so that my sum() works. =N(OFFSET(AUSREF;MATCH($A4;AUS...

Missing email content, body text, from etc
I have one user on our exchange 5.0 system thats having some weird problems. We have a exchange 5.0 box that handles internal mail. We also have pop accounts with our ISP. In the client (Outlook 2000) I get the pop account to drop all the pop email into the exchange mailbox. Everything seems to be working okay from inside the network. We have another building with computers setup. This computers (XP) have a vpn connection and outlook XP set up the same as it would be on the internal network. Users connect via the vpn connection and then open outlook. It shows their exchange box and also coll...

Looking for help with database functions
I am working with a non-profit rural community development group. We are trying to put together a "database" to put people looking for work together with people needing services. A sample would might be: Joe Blow; [carpenter, woodworker]; free on tues & thur; willing to trade (equal to $10.00 hr); References: Heidi Hoe, 555-5555, John Doh, 555-5555 ext 100 Jane Doe, needing porch repaired and general yardwork, work on your own schedule, open to Barter; References: Seymour Johnson, 555-5555, Ivan Tootall, 555-5555 Winnie Pooh, looking for someone to read news, books, a...

How do I unprotect a text box in an otherwise protected worksheet
I have many cells protected on a worksheet but also have a text box that I want unprotected. I understand locking or unlocking individual cells but can't find anything regarding locking or unlocking a text box. It appears as if the sheet is protected, all text boxes are automatically protected. Hi Gary, When you protect the Workbook, (Go to Tools, Protection, Protect Sheet) The protect sheet window will pop up, you should have some options to check or uncheck under "protect worksheet for". Un-Check the objects box. This will enable all locked cells to be protected, and ...