Forumla question

Trilback

New Member
Joined
Mar 14, 2014
Messages
22
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
I have 2016 of office at work atm for this machine.

You could try this instead.
Excel Formula:
=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)
 
Upvote 0
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.xlsm
ABCDEFG
4Valeriano, Bruno DValeriBD443266
5
1c
Cell Formulas
RangeFormula
B4B4=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))
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

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