Skip to main content
Support is Offline
Today is our off day. We are taking some rest and will come back stronger tomorrow
Official support hours
Monday To Friday
From 09:00 To 17:30
  Tuesday, 10 October 2017
  8 Replies
  9.3K Visits
0
Votes
Undo
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
EXCEL.JPG EXCEL.JPG
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
6 years ago
·
#1247
0
Votes
Undo
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.
1.png
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:
3.png
4.png
5.png
6.png
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
6 years ago
·
#1249
0
Votes
Undo
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.
Attachments (2)
Dear Sunny
Thank you very much for your help and support
yes
MID function works exactly what i was looking for
Dear sunny,
can you please help as attached
let me explain to you
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) EXCEL2.JPG
EXCEL2.JPG
Attachments (1)
6 years ago
·
#1252
0
Votes
Undo
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
6 years ago
·
#1254
0
Votes
Undo
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:
1.png 3.png
Then, type =C4 to the cell you want to always equal to the cell you entered.
4.png
Hide the original table and show only the formula table.
5.png
Hope it can do you a favor.
  • Page :
  • 1
There are no replies made for this post yet.