Time values from extracted text strings

**Steve Roth** · 12-08-2012, 11:11 AM

I'm extracting times from a text string using the Left and Right functions.

They appear as 10:30 am etc.

Excel apparently sees them as values, not text, because =text(F7,"hh:m") works properly on them. (But: they display flush left, which suggests otherwise.)

I get #value error when I:

1. Subtract one from another

2. Use timevalue on them. This whether or not I've used the text function to convert it (back?) to a string first.

No extra spaces in the strings, and I've used every conversion (value to text, text to value) and cleanup back and forth I can think of -- text, trim, clean, value, etc. I've even tried using &""" to put quotes around them.

When I just type '10:30 am in a cell, I can convert it to a value and use it numerically, no problem.

But when I copy my extracted strings, then Paste Special value-only into a new cell, they still don't act appropriately.

Spreadsheet enclosed. Any of you kind people have any ideas? Thx.

**dilipandey** · 12-08-2012, 11:37 AM

Hi Steve,

Those are still TEXTs.. see below attachment where I have done two tests:-

Test 1) written same time manually (on row 9) and compared them with your time using = sign
Result:- Are they equal = FALSE

Test 2) Tried fetching VALUE from them.
Result:- Are they time values = FALSE, because = value(<your time cell> ) given error. Used it on row 12.

See attachment for more :- time sample.xls

Regards,
DILIPandey

<click on below 'star' if this helps>

**benishiryo** · 12-08-2012, 11:40 AM

hi Steve Roth, welcome to the forum. there are 2 types of spaces in Excel, as far as i know. the normal one we know of (which is actually char 32) & another probably obtained from the system or somewhere else (char 160). both will look the same to us, but means differently to Excel. so substitute char 160 to 32 or " ", and you should get the results:
=SUBSTITUTE(TRIM(LEFT(B7,FIND("-",B7,1)-1)),CHAR(160),CHAR(32))
or
=SUBSTITUTE(TRIM(LEFT(B7,FIND("-",B7,1)-1)),CHAR(160)," ")

**Steve Roth** · 12-08-2012, 12:22 PM

@benshiryo:

THANKS! That did it. But I had to use code() to figure out what character's actually there. It's 202, not 160. Must be a Mac thing.

Why would Excel need it's own special space character? Sheesh.