Help finding the last space in a cell

C

ciaran.hudson

I need to isolate all the characters in a cell prior to the last
space.
Can anyone tell me how to find the the posistion of the last space in
the cell?

This is an example of my data:

CompanyA (e-mail address removed)
Company B (e-mail address removed)
CompC (e-mail address removed)

The output I want from my formula is:

CompanyA
Company B
CompC

I'm trying use a LEFT formula knowing the position of the last space;
but other suggestions are welcome.
 
G

Glenn

I need to isolate all the characters in a cell prior to the last
space.
Can anyone tell me how to find the the posistion of the last space in
the cell?

This is an example of my data:

CompanyA (e-mail address removed)
Company B (e-mail address removed)
CompC (e-mail address removed)

The output I want from my formula is:

CompanyA
Company B
CompC

I'm trying use a LEFT formula knowing the position of the last space;
but other suggestions are welcome.


=TRIM(SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)),""))
 
S

Shane Devenshire

Hi,

You can use the following array formula

=LEFT(A1,MAX(IF(MID(A1,ROW(A1:A99),1)=" ",ROW(A1:A99),"")))

This assumes that the entry is in B1. To enter an array press
Shift+Ctrl+Enter
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top