Can any excel experts help me?

Factual Questions
1

I know there is a way to do this, and I have done it before, but it is killing me trying to figure it out again, I havn’t used excel very much.

I am trying to create a table interface class for a database out of a description in a technical document. SO I am trying to use existing columns in a spreadsheet to create a set of strings I can cut and paste.

Say column B and column H contain numerical information.
all I want to do is something like this.

create a string (say in column Z)like “substring(X,Y);” where X is the colB number and Y is the colH number. Then I drag down through the Z column and it populates every thing for me.

I was thinking it used to be like


f="substring("&B1","&H1");"

Except the green isn’t showing up and Excel is barfing on my forumla. does anybody understand what I an talking about?

2

The substring function in Excel is “MID”

3

sorry, It’s hard to explain. I want “substring” to appear as a word in the new constructed string in the new column

4

You can’t use string functions on numbers. First convert them to strings by using the TEXT function. Then you should be home free in using any of the string functions like LEFT, RIGHT, MID, and CONCATENATE to extract and put together whatever strings you want. Oh, there’s no SUBSTRING function in Excel. Just use MID.

5

hehe I knew I couldn’t explain this very well. I don’t want to substring anything. I want to build a new string that takes data out of the number columns and puts it into a newly constructed constructed column



lets say this is a basic table
A     B                  C              Z
3     crap               7
6     crappy             9
12    crapest            25

I am trying to figure out what formula to put into column Z to make column Z look like



A    B                  C                               Z
3    crap               7                               piffle(3,7);
6    crappy             9                               piffle(6,9);
12   crapest            25                              piffle(12,25);

constructed out of columns A and C, without typing it for 350 times.

6

Col Z

=“piffle(”&trim(A2)&","&trim(C2)&") ;"

7

The trim() isn’t strictly necessary.

The OP’s goof was that the contatenation operator & needs to be between each data chunk being concatetenated together. The & is NOT a cell dereference operator.

So
=A1&A2&A3
is a perfectly good way to string three cells’ values together.

And
=“Constant value goes here”&A1&“and here”&A2&“here too”
is also valid.

Finally, you can embed spaces in formulas to make them easier to read. Excel will sometimes try to “help” by switching to range selection mode when you hit the space bar. Setting that annoyance aside, a formula like

=“Constant value goes here” & A1 & “and here” & A2 & “here too”

Looks a lot more like an expression in a normal programming language and makes syntax errors like the OP made a little more obvious for what they are; an omitted operator.

8

Thank you so much guys.I was driving me crazy trying to remember how to do it, I never would have thought of it. And I couldn’t phrase what I needed well enough for google to point me anywhere useful.