extract data up to a certain character

[markahpi]

hi all,

I'm having trouble extracting data up on a cell up to a certain
character, for example up to "/"

for example this cell data: David Staton / 102 oak st

I just need David Station and leave not / 102 oak st

thanks for any help,
mark

 

[Dave Peterson]

=TRIM(LEFT(A1,SEARCH("/",A1)-1))
is one way.

hi all,

I'm having trouble extracting data up on a cell up to a certain
character, for example up to "/"

for example this cell data: David Staton / 102 oak st

I just need David Station and leave not / 102 oak st

thanks for any help,
mark

 

[Dave Peterson]

You have other replies at your other post, too.

hi all,

I'm having trouble extracting data up on a cell up to a certain
character, for example up to "/"

for example this cell data: David Staton / 102 oak st

I just need David Station and leave not / 102 oak st

thanks for any help,
mark

 

[markahpi]

thanks dave, it works great. first time here so not sure which forum to
post.

I also forgot asking on how to extract the other part "102 oak st"
only?

 

[Dave Peterson]

=SEARCH("/",A1)

returns the position of that / character.

So
=TRIM(LEFT(A1,SEARCH("/",A1)-1))
gets the stuff before the slash.

and
=TRIM(MID(A1,SEARCH("/",A1)+1,255))

the =trim() portion will remove any leading/trailing or duplicate embedded
spaces.

the 255 is just a large enough number to get the rightmost characters.