replace numbers in excel
 

My knowledge of Excel is fair but i am having a real problrm with Find and
replace that is doing my brain in. My wife has compiled a large spreadsheet
of telephone numbers that are prefaced with the German IDD in the format
0049space0123456789. She has made two mistakes - the first in inserting the
IDD when it was not required and secondly having inserted the IDD she should
have taken the leading 0 out and did not . To correct both mistakes, she
needs to remove the 0049space. Despite me formatting the cells as text and
trying every combination, F&R fails despite my every effort. I am finding the
0049 and leaving the "replace" cell blank. No deal. Any ideas what I am doing
wrong please?

replace numbers in excel
 

Just remove the first 5 characters if the value is text:

=RIGHT(A1,10)
--
Gary's Student
gsnu200703


"nera techie nora thickiebee" wrote:

My knowledge of Excel is fair but i am having a real problrm with Find and
replace that is doing my brain in. My wife has compiled a large spreadsheet
of telephone numbers that are prefaced with the German IDD in the format
0049space0123456789. She has made two mistakes - the first in inserting the
IDD when it was not required and secondly having inserted the IDD she should
have taken the leading 0 out and did not . To correct both mistakes, she
needs to remove the 0049space. Despite me formatting the cells as text and
trying every combination, F&R fails despite my every effort. I am finding the
0049 and leaving the "replace" cell blank. No deal. Any ideas what I am doing
wrong please?

replace numbers in excel
 

Hi thanks but i don't think that works. The numbers are all different string
lengths . If I have understood the "Right" function" correctly, it "returns"
numbers from right to left of the string rather than deleting the first 5
characters (0049+space). As the individual numbers are of different lengths
it is "returning" the wrong number. Or have I got that wrong as well?

Thanks

"Gary''s Student" wrote:

Just remove the first 5 characters if the value is text:

=RIGHT(A1,10)
--
Gary's Student
gsnu200703


"nera techie nora thickiebee" wrote:

My knowledge of Excel is fair but i am having a real problrm with Find and
replace that is doing my brain in. My wife has compiled a large spreadsheet
of telephone numbers that are prefaced with the German IDD in the format
0049space0123456789. She has made two mistakes - the first in inserting the
IDD when it was not required and secondly having inserted the IDD she should
have taken the leading 0 out and did not . To correct both mistakes, she
needs to remove the 0049space. Despite me formatting the cells as text and
trying every combination, F&R fails despite my every effort. I am finding the
0049 and leaving the "replace" cell blank. No deal. Any ideas what I am doing
wrong please?

replace numbers in excel
 

Try this, then:

=MID(A1,6,255)

(takes characters from the 6th character onwards), or this:

=RIGHT(A1,LEN(A1)-5)

(takes the right end part of the string, excluding the first 5
characters).

Hope this helps.

Pete

On Jan 29, 4:31 pm, nera techie nora thickiebee
m wrote:
Hi thanks but i don't think that works. The numbers are all different string
lengths . If I have understood the "Right" function" correctly, it "returns"
numbers from right to left of the string rather than deleting the first 5
characters (0049+space). As the individual numbers are of different lengths
it is "returning" the wrong number. Or have I got that wrong as well?

Thanks



"Gary''s Student" wrote:
Just remove the first 5 characters if the value is text:

=RIGHT(A1,10)
--
Gary's Student
gsnu200703

"nera techie nora thickiebee" wrote:

My knowledge of Excel is fair but i am having a real problrm with Find and
replace that is doing my brain in. My wife has compiled a large spreadsheet
of telephone numbers that are prefaced with the German IDD in the format
0049space0123456789. She has made two mistakes - the first in inserting the
IDD when it was not required and secondly having inserted the IDD she should
have taken the leading 0 out and did not . To correct both mistakes, she
needs to remove the 0049space. Despite me formatting the cells as text and
trying every combination, F&R fails despite my every effort. I am finding the
0049 and leaving the "replace" cell blank. No deal. Any ideas what I am doing
wrong please? - Hide quoted text -- Show quoted text -