Replacing special char with comma in TEXT cell don't work (Excel2K)

[Nakis]

Hi there,
this really puzzles me.
I formatted a cell into TEXT, then typed 1'234
When Edit->Replace ' with , it will not work. The result is 1234.
I tried that with "#%&/()= instead of ' and still the same.
Can anyone tell why and what the solution is please?
Thank you all.

 

[DPlank]

What are you trying to achieve with this?

I've checked in XL2002 and 1'234 when hit with Find/Replace replacing the ' with a space returns 1 234.

If you are replacing the ' with nothing, then effectively you are removing it from the cell contents, which would be correct to return 1234.

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[TonyJollans]

Hi Nakis,

This does seem a little counter-intuitive doesn't it?

There is nothing wrong with the Replace itself - that works.

What is happening is that - despite the cell being formatted as text - the result of the replace is interpreted as a number - [blue]1,234[/blue] - and the value of that number - [blue]1234[/blue] - is placed in the cell before the cell is (re)formatted as text.

There are, I suppose, a couple of possible workarounds but why are you doing this?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[UniqueFD]

When you replace any of the characters you mention with the comma, excel is reading "1,234" (one thousand, two hundred and thirty four) and converting the cell to a number format. Your number format must be without commas for the cell to show "1234".

If you want to force the cell to be text, put the apostrophe first - ie: '1234.

If you just want the number to display with the comma, leave the cell contents as "1234" and select: Format > Cells > Number and check the "Use 1000 Separator (,)" box.

Can't be any more help without knowing exactly what you are trying to achieve.

Tony
___________________________________________________
Reckless words pierce like a sword,
but the tongue of the wise brings healing (Solomon)

 

[Nakis]

Hi all,
thanks for the replies. This is what I try to accomplish.
We are using a third party application, from where we copy some values and paste them into Excel.
Since we don't want to change our regional settings, the numbers pasted are not recognized as numbers.
Therefore we use a macro where we
a) replace '.' with ';'
b) ',' with '.'
c) ';' with ','
But since (c) above does not work, then I cannot format them as numbers.

First column indicates the numbers as copied from 3rd party application, second column how they end up after the macro, third column the desired format.
You see, in some cases it looks like it works fine!

6,399,127.23 6.399.127,23 6.399.127,23
2.330000 2330000 2,330000
2.520159874 2520159874 2,520159874
0.999863630 0,999863630 0,999863630
-3.554584 -3554584 -3,554584
0.000395 0,000395 0,000395
436.370048 436370048 436,370048

thanx

 

[KenWright]

Why do you replace anything with anything else at all, ie why not just lose all the commas or semicolons etc replacing them with nothing, and leave yourself pure numeric data. You may have to do a quick coercion of all your data to numeric from text when you replace all the commas and semis etc, but it's a real quick thing to do.

Then you can format it to your hearts content using normal Format / cells etc.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[UniqueFD]

Ken,

good idea! However, if you take a look at the data, it looks like the format being originally output is how excel, by default, breaks up numbers (full-stop/period for decimal point, commas for 1000 separator) but Nakis is switching these (ie: comma for decimal point; period for 1000 separator). I have occasionally come accross this notation before but it doesn't seem to be in my standard types in excel. Do you know if this can be achieved by changing the regional/language settings?

Tony
___________________________________________________
Reckless words pierce like a sword,
but the tongue of the wise brings healing (Solomon)

 

[UniqueFD]

Hi again - just answered my own question.

Nakis, you can change the way numbers are displayed on your PC by simply changing the regional options:

Start > Settings > Control Panel > Regional Options > Change Format of Numbers.

You can either change the region, or go into Customise and change the relevant symbols.

Hope this helps.



Tony
___________________________________________________
Reckless words pierce like a sword,
but the tongue of the wise brings healing (Solomon)

 

[TonyJollans]

Hi Nakis,

Yes that can be a problem. Without knowing the 3rd party app and/or exactly what you're doing it's difficult to advise the best approach. Are you literally (manually) cutting and pasting, or what?

Ken,

You can't just lose all the separators becuase you have to keep track of the decimal point.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[TonyJollans]

I should have refreshed before posting!

Tony,

You can't necessarily just change regional settings because it affects more than just numbers - it depends what else you're doing at the same time.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[UniqueFD]

Tony,
Thanks for your reply. I see what you mean (just tried it).

However, I think I've found an answer:

Nakis,
Try the following:
Tools > Options > International
In here, you can change the number handling, and reverse the Decimal/Thousand separators. Click OK and all the numbers in the sheet will be correctly displayed.

Tony
___________________________________________________
Reckless words pierce like a sword,
but the tongue of the wise brings healing (Solomon)

 

[UniqueFD]

Since we don't want to change our regional settings, the numbers pasted are not recognized as numbers.

[embarassed]
Just re-read your post. Ignore me completely.

Tony
___________________________________________________
Reckless words pierce like a sword,
but the tongue of the wise brings healing (Solomon)

 

[DPlank]

How about opening the file in Notepad, global find and replace there, then importing into Excel with the numbers in the format you want?

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[UniqueFD]

You could try the following:

(I have assumed that the semi-colon is the separator used in formulae in Excel - I hope this is usual for your regional settings?!)

Assuming the original text string is in A1, placing this formula in B1 will remove the first comma (or do nothing if there are no commas):

=IF(ISERROR(MID(A1;1;SUM(SEARCH(",";A1)-1))&MID(A1;SUM(SEARCH(",";A1)+1);LEN(A1)));A1;MID(A1;1;SUM(SEARCH(",";A1)-1))&MID(A1;SUM(SEARCH(",";A1)+1);LEN(A1)))

Copy B1 into C1, D1, E1 etc. until all the comma's are removed, then, in cell F1:

=(MID(E1;1;SUM(SEARCH(".";E1)-1))&","&MID(E1;SUM(SEARCH(".";E1)+1);LEN(E1)))*1

will change the period into a comma and convert the result into a number.

This is long-winded but works (on my machine, anyway!).

Tony
___________________________________________________
Reckless words pierce like a sword,
but the tongue of the wise brings healing (Solomon)