Forumla question

[Trilback]

I want to be able to make my job easier at work. I need to be able to do the following. ex. Valeriano, Bruno D

For the above last name, first name space Middle I I need to be able to copy from a web site and it show in another cell the customer the first 6 char of there last name, first letter of there fist name + middle I. How would I do that? For the above ex the answer would be ValeriBD

 

[Sufiyan97]

Try

Book8
	A	B	C
1	Valeriano, Bruno D	ValeriBD
2
3
Sheet1

Cell Formulas
Range	Formula
B1	B1	=LEFT(A1,6)&MID(TRIM(A1),SEARCH(" ",TRIM(A1))+1,1)&RIGHT(A1)

 

[Trilback]

Try

Book8
	A	B	C
1	Valeriano, Bruno D	ValeriBD
2
3
Sheet1

Cell Formulas
Range	Formula
B1	B1	=LEFT(A1,6)&MID(TRIM(A1),SEARCH(" ",TRIM(A1))+1,1)&RIGHT(A1)

Close but it did not get the Middle Int

 

[Trilback]

Kinderman, William F try another for an example

 

[Sufiyan97]

is it not what you want?

Book10
	A	B	C
1	Valeriano, Bruno D	ValeriBD
2	Kinderman, William F	KinderWF
3
4
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=LEFT(A1,6)&MID(TRIM(A1),SEARCH(" ",TRIM(A1))+1,1)&RIGHT(A1)

If not then please give some more examples

 

[Trilback]

is it not what you want?

Book10
	A	B	C
1	Valeriano, Bruno D	ValeriBD
2	Kinderman, William F	KinderWF
3
4
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=LEFT(A1,6)&MID(TRIM(A1),SEARCH(" ",TRIM(A1))+1,1)&RIGHT(A1)

If not then please give some more examples

it is but for some reason it's not catching the middle on my end.

 

[rlv01]

An alternate if your version of Excel supports the 'Let' statement.

=LET(X,SUBSTITUTE(SUBSTITUTE(TRIM(A1)," ",""),CHAR(160),""),LEFT(X,6)&MID(X,FIND(",",X)+1,1)&RIGHT(X,1))

 

[Dave Patton]

TextBefore.xlsm
	A	B
4	Valeriano, Bruno D	ValeriBD
5
1c

Cell Formulas
Range	Formula
B4	B4	=LEFT(A4,6)&MID(A4,FIND(" ",A4)+1,1)&TEXTAFTER(A4," ",2)

 

[Trilback]

An alternate if your version of Excel supports the 'Let' statement.

=LET(X,SUBSTITUTE(SUBSTITUTE(TRIM(A1)," ",""),CHAR(160),""),LEFT(X,6)&MID(X,FIND(",",X)+1,1)&RIGHT(X,1))

I have 2016 of office at work atm for this machine.

 

[Fluff]

I have 2016 of office at work

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)