remove space in excel formula

kevinkhan

New Member
Joined
Feb 24, 2010
Messages
17
ok i have this text in J3
Atomic Kitten - Whole World

I want to extract the characters after the - sysmbol (not including the first space)

I have this formula so far
="#"&RIGHT(J3,LEN(J3)-FIND("-",J3))

but its producing a result like this
# Whole World

what formula do i use to exclude the space before the first letter which in this case its W

<colgroup><col width="100"></colgroup><tbody>
</tbody>

<colgroup><col width="303"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,037
Office Version
  1. 365
Platform
  1. Windows
Add a TRIM function

Like =TRIM(RIGHT(J3,LEN(J3)-FIND("-",J3)))

or

="#"&TRIM(RIGHT(J3,LEN(J3)-FIND("-",J3)))

Depending on what you want to achieve
 
Last edited:

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,193
One more way,

="#"&REPLACE(J3,1,FIND("-",J3,1)+1,"")
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
And one more way...

If there is always a space after the dash
---------------------------------------------------------
="#"&MID(J3,FIND("-",J3)+2,99)

If the space after the dash might be missing
---------------------------------------------------------
="#"&TRIM(MID(J3,FIND("-",J3)+1,99))
 

Watch MrExcel Video

Forum statistics

Threads
1,108,911
Messages
5,525,591
Members
409,652
Latest member
strangelyangely

This Week's Hot Topics

Top