Format column with text and number

mrtobsy

New Member
Joined
Oct 16, 2006
Messages
4
Hi,

Not sure how to formulate this, but i'd like to know how to add numbers (and have it automatically updated) to a column filled with text.

Example:

I have a list like that:

john (1)
ben (2)
pete (3)
henry (4)

and i want to add edouard (3):

john (1)
ben (2)
edouard (3)
pete (3)
henry (4)

How do i do so that the numbers get updated automatically (ie pete (3) -> (4) and henry (5)-> (6))

Secondly, how can i number the cells in the columns (ie (1), (2)) automatically (if i have 150 names to list and numbered like the example above).

For my example, i assume that text+number are in the same cell. Maybe i could have one cell for each value and merge them(just thought about it), but don't if it would work and if practical or not?

Appreciate any helps,

Thanks
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, mrtobsy
Welcome to the Board !!!!!

to keep things simple I wouldn't put both items in the same cell
do you really want to use at all cost this counter-excel-intuitive solution :confused:
we can cooperate when you can clarify this: "everything is do-able"

how are you going to "add" edouard ?
seems more like you are going to "insert" the name between the others ...
needs also clarification to my sense

take a look at the function ROW() in the helpfiles

remark: "merge" is not the same as "concatenate"

example
  A     B       C           
1 names numbers concatenate 
2 john  1       john (1)    
3 ben   2       ben (2)     
4 pete  3       pete (3)    
5 henry 4       henry (4)   

test

[Table-It] version 06 by Erik Van Geit
Code:
RANGE FORMULA (1st cell)
B2:B5 =ROW()-ROW(B$1)
C2:C5 =A2&" ("&B2&")"

[Table-It] version 06 by Erik Van Geit

insert row and copy formulas
  A     B       C           
1 names numbers concatenate 
2 john  1       john (1)    
3 ben   2       ben (2)     
4       3        (3)        
5 pete  4       pete (4)    
6 henry 5       henry (5)   

test

[Table-It] version 06 by Erik Van Geit

fill in edouard
  A       B       C           
1 names   numbers concatenate 
2 john    1       john (1)    
3 ben     2       ben (2)     
4 edouard 3       edouard (3) 
5 pete    4       pete (4)    
6 henry   5       henry (5)   

test

[Table-It] version 06 by Erik Van Geit

kind regards,
Erik
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
:oops: couldn't resist to try out some code :biggrin:
assuming the items are in column one starting at row 2
Code:
Sub test()
'Erik Van Geit
'061016

Dim rng As Range        'range with data
Dim arr As Variant      'array
Dim LR As Long          'Last Row
Dim i As Long
Dim ch As Integer

LR = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A2:A" & LR)

arr = rng

    For i = 1 To rng.Rows.Count
    ch = 0
    ch = InStr(1, arr(i, 1), " (")
    If ch Then arr(i, 1) = Left(arr(i, 1), ch - 1)
    arr(i, 1) = arr(i, 1) & " (" & i & ")"
    Next i

rng = arr

End Sub

EDIT: handling empty cells (leaving empty)
Code:
Option Explicit


Sub test()
'Erik Van Geit
'061016

Dim rng As Range        'range with data
Dim arr As Variant      'array
Dim LR As Long          'Last Row
Dim i As Long
Dim ch As Integer

LR = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A2:A" & LR)

arr = rng

    For i = 1 To rng.Rows.Count
        If arr(i, 1) <> vbNullString Then
        ch = 0
        ch = InStr(1, arr(i, 1), " (")
        If ch Then arr(i, 1) = Left(arr(i, 1), ch - 1)
        arr(i, 1) = arr(i, 1) & " (" & i & ")"
        End If
    Next i

rng = arr

End Sub
 

mrtobsy

New Member
Joined
Oct 16, 2006
Messages
4
Thank you very much erik.van.geit for your help.
You're right, what i want to do is insert edouard between the others and have the "number" updated automatically. I guess concatenate is closer to what i want to do, but that'd mean that i'd have 3 columns (A w/text, B w/ number and C w/ formula) which wouldn't help me much.

Let me try something else:

If i only have two columns

A B
1 names numbers (order #)
2 john 1
3 ben 2
4 pete 3
5 henry 4



and i want column A to be associated with column B (ie john->1, ben->2, etc...). Now, i insert a text value in between A3 and A4 "Edouard", column B is (auto)updated/or i'd input the order #"3". Column B is updated to show that edouard is order #3 on the list, pete and henry would be bumped down to order #4 and #5 respectively. Is it possible to have such a system w/o having to input the numbers all the time?

Summary:

-Linking column A to B (ie john is linked to (nth))
-Inserting/Deleting a row would automatically update the other entries to show the change.

Don't know if it will yield the same result as my first post. If it has to be done on a daily basis & long term, i might have to find another solution than excel probably.

Thank for the help,

mrtobsy
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
please provide feedback on the code I posted

it was a bit of a curve of 180° I made from reply 1 to reply 2 :)
when trying out without code, I found out that inserting a row + copying down formulas would need code also, therefore my curiosity to find an "all-in-one-solution" led me to the code ...

but you didn't tell anything about it :(

so, to get further help:
put some names in column A
run the code
insert some names
run the code
provide feedback

(works nicely for me)
 

Forum statistics

Threads
1,136,266
Messages
5,674,728
Members
419,523
Latest member
Urnovio

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
Top