Excel remove characters from text string

MHau5

New Member
Joined
Oct 23, 2021
Messages
26
Office Version
  1. 2019
Platform
  1. Windows
Hello,

I have cells that contain text strings within (every cell has a different character count of text in them). The one consistent among all of them is there is a "--" between the text in the cells.

I'm trying to find a way to remove the text characters and have them placed in their own string. I have an image shown below to help.

1638116067397.png


Is there a way to have each player name moved to it's own cell without using text to columns or VBA?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Is there a way to have each player name moved to it's own cell without using text to columns or VBA?
It can be broken down with formulas into the adjacent columns, keeping the existing string where it is. If you want to clear the original cell and use it as the first name then it's text to columns, vba, or possibly power query (not something I know much about). If you don't like any of the above then I'm afraid it's cut and paste by hand.
 
Upvote 0
Try this, copied across and down.

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

21 11 29.xlsm
ABCDE
2Alex Ovechkin--Evgeny Kuznetsov--Aliaksei ProtasAlex OvechkinEvgeny KuznetsovAliaksei Protas 
3    
4Daniel Sprong--Lars Eller--Tom watsonDaniel SprongLars EllerTom watson 
5    
6Dmitry Orlov -- Nick JensenDmitry OrlovNick Jensen  
Split names
Cell Formulas
RangeFormula
B2:E6B2=TRIM(MID(SUBSTITUTE("--"&$A2,"--",REPT(" ",100)),100*COLUMNS($B:B),100))
 
Upvote 0
You're welcome. Thanks for letting us know. :)
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

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