can anyone help me
i want to split word into column and than pickup value
e.g:
if i already assign value to each alphabet A-Z 1 - 26
when i enter any word in cell c3
e.g: STRAWBERY in cell C3
than it should split in colums automatically e.g d4 E4 F4 G4 H4 I4 J4 K4 L4
S T R A W B E R Y
i want to split word into column and than pickup value
e.g:
if i already assign value to each alphabet A-Z 1 - 26
when i enter any word in cell c3
e.g: STRAWBERY in cell C3
than it should split in colums automatically e.g d4 E4 F4 G4 H4 I4 J4 K4 L4
S T R A W B E R Y
0
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.
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
e.g
i write mango in cell c3
it automatically split M A N G O in columns
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
e.g
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.
Apply =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]
Hope it works for you.
Apply =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]
Hope it works for you.
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:
Then, type =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.
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:
Then, type =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.
- Page :
- 1
There are no replies made for this post yet.
Please login to post a reply
You will need to be logged in to be able to post a reply. Login using the form on the right or register an account if you are new here. Register Here »