Trim function ?

[bflan0524]

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

 

[Canapone]

Hi,

one of the formulas could be:

=LEFT(A2,1)

Regards

 

[bflan0524]

perfect...ty so much

 

[Canapone]

Ciao,

thanks for sharing feedback.

 

[FDibbins]

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)

 

[Rick Rothstein]

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)

 

[FDibbins]

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

 

[Rick Rothstein]

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).

 

[hiker95]

bflan0524,

How about something like this?

Excel 2007
	A	B	C	D
1	4-Very Satisfied	4	4	4
2	14-Very Satisfied	14	14	14
3	23414-Very Satisfied	23414	23414	23414
4
Sheet1

Cell Formulas
Range	Formula
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.

 

[Aladin Akyurek]

@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.