Hello, If you want to split a word into columns with single character, you can apply Kutools for Excel's Split Cells to quickly separate the word as below screenshot 1 shown.


If you want to extract number based on the split alphabetical character, you can apply Kutools for Excel's Look for a value to find the first one number based on the first one character, then change the table reference of formula to column absolute reference. See the screenshots:








If it works for you,please let me know . Thank you.

i know about this procedure in it, we have to use split cell function every time
what i am look for is
i want to use a formula or group of formulas, to split cell into column as a value or text enters
de exemplu,
i write mango in cell c3
it automatically split M A N G O in columns

Here are some formulas may can help you solve the question.
Aplică =MID($D$1, COLUMNS($D$1:D$1), 1) to multiple cells to split the text string in cell D1, you can drag the formula to as many cells as you can. See screenshot:
[attachment]ecb6d034e30df9526ec3b856c762c2f9[/attachment]
Then use this formula =IF(AND(CODE(D2)<91,CODE(D2)>64),CODE(D2)-64,IF(AND(CODE(D2)<123,CODE(D2)>96),CODE(D2)-96,0)) in a cell, and drag auto fill handle to fill this formula right. D2 is the cell places the first character of the split string. See screenshot:
[attachment]9f8f8bf11a12f1566624df9bf22e8e55[/attachment]

Sper că lucrează pentru tine.

Dear Sunny
Thank you very much for your help and support
da
MID function works exactly what i was looking for

Dear sunny,
can you please help as attached
hai sa-ti explic
if i enter a value in cell c4
than it automatically show up in cell c7 and than in created table in each cell add one to previous number e.g c7 =1 than c8= (1+1)=2 THAN E8=2+1(3)

Excuseme, do you mean to auto add the value which you entered in cell C4 to every number in the table? For instance, if the cell C4 contains 2, then add 2 to each value in the table?

Yes Sunny true
For instance if the value of cell c4 contains 2 than add 1 and show 3 in cell d4
can i have your email please if you don't mind
saqibmk22@gmail.com

Sorry there is no direct way I can find to solve your question. But if do not mind, you can use some helper rows and formulas to solve it.
Firstly, insert some blank row and type this formula =B8+IF($C$4="",0,1) below the original table, and drag fill handle over the cells you want to use. B8 is the cell you use to add 1, and C4 is the cell you enter value. See screenshot:



Apoi, tastați = C4 to the cell you want to always equal to the cell you entered.


Hide the original table and show only the formula table.


Hope it can do you a favor.