Ask Your Question
0

Concatenation of multiple cells on multiple rows

asked 2017-08-15 16:30:28 +0200

aircraftclocks gravatar image

I am trying to concatenation two cells together with a character between them, for multiple rows, in order to create part numbers in my spreadsheet.

1A with / and 1 to create the result 1A/1 for example.

In order to replace the calculated value in place of the formula used, you can select F9. This works on each set of cells you have applied this function to.

How can you apply the effect of the F9 function as part of the formula for a large number of following rows?

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
1

answered 2017-08-15 19:19:28 +0200

m.a.riosv gravatar image

There is also another option to convert cells on their values:

Menu/Data/Calculate/Formula to value.

edit flag offensive delete link more
1

answered 2017-08-15 19:23:14 +0200

librebel gravatar image

updated 2017-08-15 19:24:23 +0200

Hello @aircraftclocks,

suppose that your cell A1 contains the string "1A", your cell A2 contains the string "/", and your cell A3 contains the integer 1, then to concatenate these three cells you could create a formula such as : =A1 & A2 & A3 and it comes out as "1A/1"

edit flag offensive delete link more
0

answered 2017-08-15 18:34:20 +0200

Lupp gravatar image

updated 2017-08-15 19:48:42 +0200

The way you mentioned is just a shortcut usable while editing a formula. Ther is no "F9 function as part of the formula. F9 replaces the one formula you are editing with its result.

To get "pure data" for calculated ranges use 'Copy' --> 'Paste Special...' Results only.
(Prefer the way @m.a.riosv explained.)

I would anyway prefer to keep the parts. Parsing compound information for its parts is much more complicated (less supported) than concatenation to get compounds. (--> keep parts basically, create compounds as redundant info.)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-08-15 16:30:28 +0200

Seen: 1,470 times

Last updated: Aug 15 '17