#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

#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

#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

#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

#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

#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

#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

#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

#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