|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 22nd, 2007, 07:59 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE error
Hi!
I have a time table with: A B 1 11:55:00 AM 2:20:00 PM If aplied the formulae =(B1-A1) return #VALUE error, because there are one space before first digit. If erased the space, work fine. With Find and Replace don't solve the problem. Is possible to solve this problem automaticly, please? Thanks in advance. an 
|
|
#2
November 22nd, 2007, 08:14 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#VALUE error
One way
=TRIM(B1)-TRIM(A1) Mike "an" wrote: Hi! I have a time table with: A B 1 11:55:00 AM 2:20:00 PM If aplied the formulae =(B1-A1) return #VALUE error, because there are one space before first digit. If erased the space, work fine. With Find and Replace don't solve the problem. Is possible to solve this problem automaticly, please? Thanks in advance. an
|
|
#3
November 22nd, 2007, 08:15 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#VALUE error
VALUE(B1)-A1
best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "an" wrote in message ... Hi! I have a time table with: A B 1 11:55:00 AM 2:20:00 PM If aplied the formulae =(B1-A1) return #VALUE error, because there are one space before first digit. If erased the space, work fine. With Find and Replace don't solve the problem. Is possible to solve this problem automaticly, please? Thanks in advance. an
|
|
#4
November 22nd, 2007, 08:36 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#VALUE error
Thanks for reply.
Don't result. The original format cell is General After change to Custom h:hh:mm AM/PM or Time format don't result too. Thanks more one time. an "Mike H" wrote: One way =TRIM(B1)-TRIM(A1) Mike "an" wrote: Hi! I have a time table with: A B 1 11:55:00 AM 2:20:00 PM If aplied the formulae =(B1-A1) return #VALUE error, because there are one space before first digit. If erased the space, work fine. With Find and Replace don't solve the problem. Is possible to solve this problem automaticly, please? Thanks in advance. an
|
|
#5
November 22nd, 2007, 08:38 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#VALUE error
Thanks for reply.
Don't result. The original format cell is General. After change to Time format or h:mm:ss AM/PM don't result too. Thanks you. an "Bernard Liengme" wrote: VALUE(B1)-A1 best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "an" wrote in message ... Hi! I have a time table with: A B 1 11:55:00 AM 2:20:00 PM If aplied the formulae =(B1-A1) return #VALUE error, because there are one space before first digit. If erased the space, work fine. With Find and Replace don't solve the problem. Is possible to solve this problem automaticly, please? Thanks in advance. an
|
|
#6
November 22nd, 2007, 09:08 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#VALUE error
From where do you get the data?
If imported. perhaps the space is a non-breaking space. TRIM or CLEAN functions will not remove those. Select and EditReplace What: Alt + 0160(using NumPad) With: leave blank Replace all Does that compute any better? Gord Dibben MS Excel MVP On Thu, 22 Nov 2007 12:36:01 -0800, an wrote: Thanks for reply. Don't result. The original format cell is General After change to Custom h:hh:mm AM/PM or Time format don't result too. Thanks more one time. an "Mike H" wrote: One way =TRIM(B1)-TRIM(A1) Mike "an" wrote: Hi! I have a time table with: A B 1 11:55:00 AM 2:20:00 PM If aplied the formulae =(B1-A1) return #VALUE error, because there are one space before first digit. If erased the space, work fine. With Find and Replace don't solve the problem. Is possible to solve this problem automaticly, please? Thanks in advance. an
|
|
#7
November 22nd, 2007, 09:48 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#VALUE error
Ok, GD.
Exactly. Work fine! Thank you very much. an "Gord Dibben" wrote: From where do you get the data? If imported. perhaps the space is a non-breaking space. TRIM or CLEAN functions will not remove those. Select and EditReplace What: Alt + 0160(using NumPad) With: leave blank Replace all Does that compute any better? Gord Dibben MS Excel MVP On Thu, 22 Nov 2007 12:36:01 -0800, an wrote: Thanks for reply. Don't result. The original format cell is General After change to Custom h:hh:mm AM/PM or Time format don't result too. Thanks more one time. an "Mike H" wrote: One way =TRIM(B1)-TRIM(A1) Mike "an" wrote: Hi! I have a time table with: A B 1 11:55:00 AM 2:20:00 PM If aplied the formulae =(B1-A1) return #VALUE error, because there are one space before first digit. If erased the space, work fine. With Find and Replace don't solve the problem. Is possible to solve this problem automaticly, please? Thanks in advance. an
|
|
#8
November 22nd, 2007, 11:58 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#VALUE error
Thanks for the feedback.
I should have said "if imported or copied from a web-site" because that's the usual place to collect non-breaking HTML character((   Happy to help. Gord On Thu, 22 Nov 2007 13:48:01 -0800, an wrote: Ok, GD. Exactly. Work fine! Thank you very much. an "Gord Dibben" wrote: From where do you get the data? If imported. perhaps the space is a non-breaking space. TRIM or CLEAN functions will not remove those. Select and EditReplace What: Alt + 0160(using NumPad) With: leave blank Replace all Does that compute any better? Gord Dibben MS Excel MVP On Thu, 22 Nov 2007 12:36:01 -0800, an wrote: Thanks for reply. Don't result. The original format cell is General After change to Custom h:hh:mm AM/PM or Time format don't result too. Thanks more one time. an "Mike H" wrote: One way =TRIM(B1)-TRIM(A1) Mike "an" wrote: Hi! I have a time table with: A B 1 11:55:00 AM 2:20:00 PM If aplied the formulae =(B1-A1) return #VALUE error, because there are one space before first digit. If erased the space, work fine. With Find and Replace don't solve the problem. Is possible to solve this problem automaticly, please? Thanks in advance. an
|
|
#9
November 23rd, 2007, 01:09 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#VALUE error
Yes, indeed was copied of the Web.
Thanks an "Gord Dibben" wrote: Thanks for the feedback. I should have said "if imported or copied from a web-site" because that's the usual place to collect non-breaking HTML character(( ) Happy to help. Gord On Thu, 22 Nov 2007 13:48:01 -0800, an wrote: Ok, GD. Exactly. Work fine! Thank you very much. an "Gord Dibben" wrote: From where do you get the data? If imported. perhaps the space is a non-breaking space. TRIM or CLEAN functions will not remove those. Select and EditReplace What: Alt + 0160(using NumPad) With: leave blank Replace all Does that compute any better? Gord Dibben MS Excel MVP On Thu, 22 Nov 2007 12:36:01 -0800, an wrote: Thanks for reply. Don't result. The original format cell is General After change to Custom h:hh:mm AM/PM or Time format don't result too. Thanks more one time. an "Mike H" wrote: One way =TRIM(B1)-TRIM(A1) Mike "an" wrote: Hi! I have a time table with: A B 1 11:55:00 AM 2:20:00 PM If aplied the formulae =(B1-A1) return #VALUE error, because there are one space before first digit. If erased the space, work fine. With Find and Replace don't solve the problem. Is possible to solve this problem automaticly, please? Thanks in advance. an
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
