Splitting Cell Value

[MGumbrell]

I have a range of cells (A1:A77) that each contain a value such as:-

N002 Lift Lobby
N003 Corridor
N006 Shower
etc
etc

What I would like
Is to extract this information from each cell to the adjacent cells so that
Range B1:B77 contains
N002
N003
N006
etc
etc

Range C1:C77 conatins
Lift Lobby
Corridor
Shower
etc
etc

Thanks, Matt

 

[Brianwarnock]

If the first string is always the same length then it is a simple case of using Left and Mid functions if not then Find will also be required to determine the positition of the first space.

Brian

 

[stopher]

If the space is in the same place, anther option is to use:
Data=>Text to columns=>Fixed width etc.

Chris

 

[MGumbrell]

Guys

I have just checked and the first string is not always the same length.

Can you please tell me how to use the first space as in the:-
Data=>Text to columns=>Fixed width etc.
mentioned.

Thanks, Matt

 

[Kiwiman]

Howzit

You can do this (as mentioned by BrianWarnock) - it assumes a space between the two "values"

For the values in column B:

=LEFT(A1,FIND(" ",A1)-1)

For the values in column C

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

 

[Brianwarnock]

Hi Kiwi,
I did suggest that in post 2 but he doesn't seem to like that idea.

BTW I've remembered that Find is case sensitive so he may prefer to use Search

Brian

 

[Kiwiman]

Hi Brian

Yes saw that. Sorry, initially posted without your reference to your name - went back and edited it.

Yes Find is case sensitive - really threw me when I first used it (until I looked at the help about it). What is is with guys who don't read instructions???

Never used Search myself - but I'll look into it now - looks fairly useful.

Cheers for that

 

[Brianwarnock]

I wasn't after claiming bragging rights , and I did suggest Mid instead of Right, just pointing out that he didn't seem to like that route for some reason.

Brian

 

[MGumbrell]

Your a clever bunch.

Thank you guys.

Regards, Matt