Trim function ?

bflan0524

Board Regular
Joined
Oct 7, 2016
Messages
192
Office Version
  1. 2010
i am not sure if this would be a trim function or not. if somebody could help that would be great.
if i have in column A the following 4-Very Satisfied. how would i get that column to just show the 4 in another column?

thank you
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
If there might be more than a 1-digit number, that wont work...
14-Very Satisfied
will return 1

If there will always be a -, you can use that to ID where the number ends...

=left(A2,search(("-",A2)-1)
 
Upvote 0
If there might be more than a 1-digit number, that wont work...
14-Very Satisfied
will return 1

If there will always be a -, you can use that to ID where the number ends...

=left(A2,search(("-",A2)-1)
One parenthesis too many, here is the corrected formula using FIND instead of SEARCH...

=LEFT(A2,FIND("-",A2)-1)
 
Upvote 0
ooops - again, thanks

I have got into the habit of using SEARCH, because it is not case-sensitive, so now, unless I specifically need to check for "case", I use SEARCH
 
Upvote 0
I have got into the habit of using SEARCH, because it is not case-sensitive, so now, unless I specifically need to check for "case", I use SEARCH
While I have seen others claim the contrary, it is my belief that FIND is faster than SEARCH because it has much less to do under the hood in order to perform its intended function (at least that is the case in the VBA world with functions having options related to case sensitivity, so it seems natural to assume that the same would apply in the formula world).
 
Upvote 0
bflan0524,

How about something like this?


Excel 2007
ABCD
14-Very Satisfied444
214-Very Satisfied141414
323414-Very Satisfied234142341423414
4
Sheet1
Cell Formulas
RangeFormula
B1=IFERROR(LEFT(A1,IFERROR(FIND("-",A1)-1,FIND("-",A1)-1)),"")+0
C1=VALUE(TRIM(MID(SUBSTITUTE($A1,"-",REPT(" ",100)),1,100)))
D1=SUBSTITUTE(TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99)),198),99)),"-","")+0



The formulae in cells B1, C1, and, D1, copied down.
 
Upvote 0
@hiker95

Why to go to such lengths?

B1 is expensive...

Either...

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

Or...

=LEFT(A1,SEARCH("-",A1&"-")-1)

would suffice as suggested by other helpers.

The formulas that you offer in C1 and D1 looks like we are doing "numerology". The 100 of C1 becomes for no obvious reason 99 in D1, for example.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top