Shortening Name/Trim?

[ardykav]

Hi,

Currently i have a lookup that detects a tab name. However the tab names naming convention is First Name Surname Week 1 or First Name Surname Week 2

I just want to have the first name and surname and leave off the Week 2 part.

This is the current formula that i use

=IF(D2=0, "",INDIRECT("Controls!A" & H2))

What do i need to place in this for it to get the required result? I presume its Trim of some sort?

thanks

 

[mrmmickle1]

You might consider incorporating a string function like Mid() or Left():

Excel 2010
	A	B	C
1	First Name Surname Week 1	First Name Surname	First Name Surname
2	First Name Surname Week 2	First Name Surname	First Name Surname
3	First Name Surname Week 3	First Name Surname	First Name Surname
4	First Name Surname Week 4	First Name Surname	First Name Surname
5	First Name Surname Week 5	First Name Surname	First Name Surname
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT(A1,LEN(A1)-8)
C1	=MID(A1,1,LEN(A1)-8)

 

[ardykav]

Thanks but that doesnt seem to work. I have a person named Alan Green Week 1 however when i use your formulas below both formulas produce the same result? What am I doing wrong?

You might consider incorporating a string function like Mid() or Left():

Excel 2010

	A	B	C
First Name Surname Week 1	First Name Surname	First Name Surname
First Name Surname Week 2	First Name Surname	First Name Surname
First Name Surname Week 3	First Name Surname	First Name Surname
First Name Surname Week 4	First Name Surname	First Name Surname
First Name Surname Week 5	First Name Surname	First Name Surname

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=LEFT(A1,LEN(A1)-8)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=MID(A1,1,LEN(A1)-8)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]