Using "Left" Function 3

[bigmerf]

I have a spreadsheet with multiple columns of data. In one of these columns, I have data that should technically be represented in separate cell columns.

For example: cell "D4" contains both the invoice # and invoice date. It looks like this: "123456 3/2/05"

What I want to do is take this cell "D4" for example, and use some sort of function that will take every character starting from the left until the pointer hits "spaces" (" "). Once the pointer hits a space, I will know when the invoice number ends, and the invoice date starts. I tried using the LEFT(D4,"") function, but I'm getting an error I believe because it doesn't know how to read the "spaces" or ("") for that matter.

Is there a way to use the left function to search for the next "space", or is there a reverse concatenation function I could use?

Please help!!

 

[dhulbert]

WLTC

Use left(D4, (Find(" ",D4)))

This works for Excel 2003 at least.

 

[bigmerf]

Man that's awesome......and a life save might I add. Thanks a bunch. Star for you!

 

[bluedragon2]

One other thing you can do is text to columns using the space as a delimiter.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[KenWright]

It would make more sense to use Data / Text To Columns, because that will do the whole lot in one go, and also it will convert your date field thyat is currently text into real dates for you and save you having to do any work.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[bigmerf]

Wow! The Text To Column feature is also pretty cool..... This is a pretty sweet function. Stars for all.

Thanks everyone for your help!!!

 

[SilentAiche]

All,

Text to Columns works a lot better (meaning it works!) than what I tried after reading the first two posts. I, too, wanted to offer WLTC a way to preserve his dates as dates. The answer seemed obvious:

turn Left(D4, (Find(" ",D4)))

into

Right(D4, (Find(" ",D4))). No, uh uh.

Using the original example of 123456 3/2/05:

RIGHT returns 56 3/2/05. Apparently the Right function counts the number of places (from the #$%& LEFT) to the Space (9), and then returns that many characters from the right.

So, what is the opposite of left?

Tim

 

[AnotherHiggins]

SilentAiche,

try this:
=Right(D4, len(D4)-(Find(" ",D4)))

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[AnotherHiggins]

Sorry, a bit more explanation:

RIGHT IS the opposite of LEFT, but using FIND counts the places from the right! Get it?

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[SilentAiche]

Got it - thanks.

I was about to experiment with [red]TFEL[/red] and [red]DNIF[/red].

Tim

 

[AnotherHiggins]

but using FIND counts the places from the right!

erm, I guess you figured out that should read 'using FIND counts the places from the LEFT'. Oops.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[SilentAiche]

Yup (I knew what you meant).

Tim