# Forumla question



## Trilback (Dec 14, 2022)

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 (Dec 14, 2022)

Try

Book8ABC1Valeriano, Bruno DValeriBD23Sheet1Cell FormulasRangeFormulaB1B1=LEFT(A1,6)&MID(TRIM(A1),SEARCH(" ",TRIM(A1))+1,1)&RIGHT(A1)


----------



## Trilback (Dec 14, 2022)

Sufiyan97 said:


> Try
> 
> Book8ABC1Valeriano, Bruno DValeriBD23Sheet1Cell FormulasRangeFormulaB1B1=LEFT(A1,6)&MID(TRIM(A1),SEARCH(" ",TRIM(A1))+1,1)&RIGHT(A1)


Close but it did not get the Middle Int


----------



## Trilback (Dec 14, 2022)

Kinderman, William F try another for an example


----------



## Sufiyan97 (Dec 14, 2022)

is it not what you want?

Book10ABC1Valeriano, Bruno DValeriBD2Kinderman, William FKinderWF34Sheet1Cell FormulasRangeFormulaB1:B2B1=LEFT(A1,6)&MID(TRIM(A1),SEARCH(" ",TRIM(A1))+1,1)&RIGHT(A1)

If not then please give some more examples


----------



## Trilback (Dec 14, 2022)

Sufiyan97 said:


> is it not what you want?
> 
> Book10ABC1Valeriano, Bruno DValeriBD2Kinderman, William FKinderWF34Sheet1Cell FormulasRangeFormulaB1:B2B1=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 (Dec 14, 2022)

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 (Dec 14, 2022)

TextBefore.xlsmAB4Valeriano, Bruno DValeriBD51cCell FormulasRangeFormulaB4B4=LEFT(A4,6)&MID(A4,FIND(" ",A4)+1,1)&TEXTAFTER(A4," ",2)


----------



## Trilback (Dec 15, 2022)

rlv01 said:


> 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 (Dec 15, 2022)

Trilback said:


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


----------



## Trilback (Dec 14, 2022)

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 (Dec 15, 2022)

Trilback said:


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



Post some more samples of your data.


----------



## rlv01 (Dec 15, 2022)

Trilback said:


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



You could try this instead. 

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


----------



## Dave Patton (Dec 15, 2022)

It would help if you posted an extract of your sheet with the forum's tool named XL2BB.
You could also identify key characters. If you have hidden special characters a formula or UDF can help.
TextBefore.xlsmABCDEFG4Valeriano, Bruno DValeriBD44326651cCell FormulasRangeFormulaB4B4=LEFT(A4,6)&MID(A4,FIND(" ",A4)+1,1)&TEXTAFTER(A4," ",2)E4E4=CODE(MID(A4,10,1))F4F4=CODE(MID(A4,11,1))G4G4=CODE(MID(A4,12,1))


----------

