Getting Data out of cells

[ShaneBland]

I am not sure how complicated this will be. Here is what I am trying to do.

I have a worksheet with 3 columns each contaning text data. Each row is a seperate "data record". I want to create a macro or function that will take the value of each column in each row and spit out a text snippet with some other information.

Here is an example. Here is one row showing the values from each column.

Gig7/24 710 idsr4ap1-eth0

I want to then take this data and create a text snippet that looks like this below.
"
!
interface Gig7/24
switchport access vlan 710
desc idsr4ap1-eth0
!
"
Anyone have any ideas that can help me out?

 

[NBVC]

Assuming your source data is in A1:C1, then try:
=CHAR(34)&CHAR(10)&"!"&CHAR(10)&"interface "&A1&CHAR(10)&"switchport access vlan "&B1&CHAR(10)&"desc "&C1&CHAR(10)&"!"&CHAR(10)&CHAR(34)

You will have to format the cell the the text alignment is wrapped... Format|Cells select the Alignment tab and select Wrap Text.

You may to adjust the column width manually after.


EDIT:

Forgot to add the "desc" part in front of last string.

Formula above is fixed.

 

[Jim-in-I]

Assuming that your data is in A2 thru C2, you might try this...

=CONCATENATE("Interface ",A2&CHAR(10),"Switchport Access vlan ",B2&CHAR(10),"Desc ",C2)

Ensure that the cell where you enter this formula was "Wrap Text" selected.

Hope it helps,
Jim

 

[ShaneBland]

Is there anyway to put that in some sort of loop to go through all rows and outut that to one cell or text area?

 

[NBVC]

Is there anyway to put that in some sort of loop to go through all rows and outut that to one cell or text area?

Can't you just copy down the formula?

 

[ShaneBland]

Yeah but I am looking to take the text snippet that is output from that formula from each row and combine it into one text snippit.

 

[Norie]

Shane

What do you mean by a 'snippit'?

Do you want the result in one cell or multiple cells?

By the way NBVC's formula does return the result in one cell as far as I can see.

 

[ShaneBland]

I would like to have the results from all rows put into one cell.