After Right/find formula then splitt to diffrent colums

[TDNova]

Hello, im making a big database and encountered a problem, currently very new to excel (1week) and hungry to learn more.

i got this text for example "TEL 14-30/11 (113-57B)1122,1123,1124,1125" and ive splittet it to 4 colums,

tel 14-30/11 113-57b 1122,1123,1124,1125 with diffrent formulas. but on the last one i want it to splitt into more colums, so each nr got their own.


Formulas ive been using.
tel :=LEFT(FJ4;FIND(" ";FJ4) -1)
14-30/11:=MID(FJ4;FIND(" ";FJ4)+1;FIND(" ";FJ4;FIND(" ";FJ4)+1)-FIND(" ";FJ4))
113-57b:=MID(FJ4;FIND("(";FJ4)+1;FIND(")";FJ4)-FIND("(";FJ4)-1)
1122,1123,1124,1125 :=MID(FJ4;FIND(")";FJ4)+1;20)

On the last formula is there a way to make it splitt up after ever "," ?

 

[jtakw]

Hi,

If all your data strings are in the Exact same format as the sample you gave, you can use this formula copied across as far as needed to extract the data as you described:

Excel 2010
	FJ	FK	FL	FM	FN	FO	FP	FQ
4	TEL 14-30/11 (113-57B)1122,1123,1124,1125	TEL	14-30/11	113-57B	1122	1123	1124	1125
Sheet11

Cell Formulas
Range	Formula
FK4	=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($FJ4,"(",""),")"," "),","," ")," ",REPT(" ",100)),COLUMNS($FK:FK)*100-99,100))

EDIT: Don't forget to change the comas in my formula to semicolons for your region.
It would look something like this:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($FJ4;"(";"");")";" ");",";" ");" ";REPT(" ";100));COLUMNS($FK:FK)*100-99;100))

 

[TDNova]

Hi,

If all your data strings are in the Exact same format as the sample you gave, you can use this formula copied across as far as needed to extract the data as you described:

Excel 2010

	FJ	FK	FL	FM	FN	FO	FP	FQ
TEL 14-30/11 (113-57B)1122,1123,1124,1125	TEL	14-30/11	113-57B

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

[TD="align: right"]1122[/TD]
[TD="align: right"]1123[/TD]
[TD="align: right"]1124[/TD]
[TD="align: right"]1125[/TD]

</tbody>

Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]FK4[/TH]
[TD="align: left"]=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($FJ4,"(",""),")"," "),","," ")," ",REPT(" ",100)),COLUMNS($FK:FK)*100-99,100))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



EDIT: Don't forget to change the comas in my formula to semicolons for your region.
It would look something like this:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($FJ4;"(";"");")";" ");",";" ");" ";REPT(" ";100));COLUMNS($FK:FK)*100-99;100))

Okey thanks alot! I appreciate your help.
I will give it a try =)

 

[TDNova]

jtakw thanks ! it worked perfectly! it will save me alot of work aswell. I need to sit down and break down the formula to figure out how it works
then i can maybe start to build my own formulas like that.

i understand the idea and progress of the formula but im not totally familiar with all the commands/formulas yet, but that's the fun part!

Have a nice day!

 

[jtakw]

You're welcome, welcome to the forum.

Thank you, you have a nice day also.