extracting numbers only from mixed character cell

[barry2104]

Hi all
In cell A1 to A3 I have the text:
1.376,00 EUR
-386,40 EUR
12,06 EUR

I wish to convert this column into a new column (B) which simply reads
€1,376.00
-€386.40
€12.06

...in euro-currency format but preferably american number system, as most spreadsheets I use require this, and I find that switching the language/regional settings of my entire system/computer too much work if needing to do it 10 times a day

Is there a formula that can even do this? (note there is a negative value in some cells!)

On this side note, is there an easier way (Excel 2007) to make this switch so that my cells in the CURRENT/PARTICULAR worksheet display as european numbers/currency settings, whilst having another worksheet/.xls file open parallel which has the american/standard decimal place setting?

[NBVC]

I am not sure if there is a better way.. but if your data starts in A1, then use formula in another column:

Please Login or Register  to view this content.

copied down

[barry2104]

thanks for the (lengthy but accurate) formula!

I see that you've inserted it as a TEXT format though. I think this means that I can't apply a conditional format to the cells/column to show in red those values which are negative...

is it possible to have this output as a number, which I can simply edit the format with under format settings?

[NBVC]

Yes remove the TEXT part:

Please Login or Register  to view this content.

[Haseeb Avarakkan]

May be this?

Please Login or Register  to view this content.

Change the format.

[barry2104]

Thanks Haseeb and NBVC
that function from Haseeb looks to be a simpler function but for some reason I can't copy (CTRL+C) the cell and paste (CTRL+V) into another cell (to duplicate/replicate the formula). Can either of you tell me why this might be?
I AM able to drag the cell downwards from the bottom right cell grip and get the formula to replicate, but with (CTRL+C) then (CTRL+V) I just get the result from the copied cell (as text) pasted in the new cell....?

[NBVC]

...and you can with my formula?

[barry2104]

...and you can with my formula?

sorry, no I get the same problem with both formulas... Is it likely a setting of mine which has gone AWOL or is it something in the formula?
(does copy/paste work for you?)

[NBVC]

It works fine for me if a copy the cell with the formula and select another cell and paste it onto that cell (not in the formula bar).

[barry2104]

wierd... haven't experienced this before. Not sure how to fix it!
Forgot to mention that the cells which contain this formula are between 2 and 6 cells which are merged into 1. (each new entry has up to 6 rows of data, and shares 3 different columns)

take a look at the attachment and see if that makes it clearer

I've though of a workaround - in the attachment you see two new columns (red heading). The first one was what I was originally hoping for but get the aforementioned problem. The last column is the workaround, simly unmerging the joined cells. This is fine, but I was just curious as to whether it's the formula or my system/settings which prohibit this copy/paste!

[NBVC]

Yeah, it is because your merged cells are of different sizes. If you copy top "merged" cells in column E to last "merged" cells in E, then it works.. because they are the same size.

It is not really a good idea to use merged cells in Excel when dealing with data manipulation.. as you see it causes more problems then benefits...

the workaround is the way to go, you can always colour the cells white to remove the gridlines in that column to make them appear merged.