convert a concatenated string to an absolute string? 11

[loganswell]

Hi,

I feel sure this should be possible in Excel! ...

If cell A1 has value "Hello" and cell B1 has value "World" then in C1 you can enter the formula =A1&" "&B1 and get "Hello World" to appear in C1.

However my question is... How do I make the reference "Hello World" a permanent string value instead of the formula.

I hope I have worded my query properly. I cannot think of another way of expressing what it is I want to achieve.

Many thanks!

Jim

 

[xlbo]

Edit>Copy
Edit>PasteSpecial>Values

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[arthurbr]

Or
Select the cell
Place your mouse ponter against the upper edge of the selection
Move the selected cell to the desired place ( could be the same just move a little and place back) while holding the right mouse button down
When you release the button a nice menu will appear
Choose " copy here as values only"
Voilà

 

[Zathras]

Or...

Simply select the cell and press F2, F9, and Enter. (In that order, not all at the same time.)

 

[loganswell]

These responses have been wonderful! Thank you all so much )

 

[arthurbr]

Thx Zathras, that was a nice one, although I don't undersatnd the rationale
Cheers

 

[Zathras]

F2 = Change to edit mode
F9 = Calculate: Replaces formula with result (*)
Enter = Leave edit mode and update cell data

(*) That's just the way Excel works. Handy, no?

If all you want is to see the calculated result without altering the cell contents then press Escape instead of Enter. For example, if the cell is formatted as a date and contains a formula (e.g. =A1+5 for 5 days after the date in A1), you can press F2 followed by F9 to see the internal number and then press Escape so as not to change the formula into a hard-coded date.

 

[xlhelp]

If you need to do this on a regular basis, as I do, then add a paste values icon to the toolbar. That way you just highlight, copy and paste values.

Is mouse faster or keyboard? OK, ok, I don't want to start a controversy. Us diehard keyboard users are sometimes anti-rodents.

Member AAA - Abolish Abused Abbreviations

 

[Zathras]

I agree - I have the paste values icon on my toolbar. Very handy. Another one I use a lot is the "Show All" button since I make regular use of the auto filter feature.

 

[arthurbr]

Hé Loganswell, give those guys a star, they are super !

 

[loganswell]

Hi arthurb!

Yes they sure are. I didn't know about the star thing however I did gratefully acknowledge their responses. )

 

[KenWright]

Just for completeness you also have a VBA option open to you, and if this is something you do regularly (I do this stuff almost every day in some speradsheet or other), then you may want to consider having a macro tied to a keyboard shortcut, eg:-

Sub PasteValues()

    Application.ScreenUpdating = False

    With Selection
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With

    Application.CutCopyMode = False
    Application.ScreenUpdating = False

End Sub

I simply highlight the range in question and hit my keyboard shortcut of CTRL+SHIFT+P and it pastes that entire section in situ as hardwired data.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

I agree but personally, I just have the :

Selection.PasteSpecial Paste:=xlPasteValues

set up as Ctrl+z so I can do

Ctrl+c (copy)
Ctrl+z (pastespecial>values)

very very quick (as long as you are happy to use the menu item as 'Undo' rather than the kyboard shortcut)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Instead of using PasteSpecial, I have

Selection.Value = Selection.Value

( just for interest )

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[kjv1611]

Hey, does anyone here use Ctrl+R? That'd be cool, hu?

j/k ... I just found it amusing the differences: I use ctrl+z.. I use ctrl+shift+p.... and so on.

Anyway, I know I've learned some stuff here with the F2/F9/Enter stuff, and even the right-click/drag trick.

Thanks all!

 

[firefytr]

Ctrl + r is the default for Fill Right.

And .Value = .Value is probably the best way (IMHO) via code. I just use the keyboard shortcut though..

Ctrl + C, Alt + E, S, V.

-----------
Regards,
Zack Barresse

 

[KenWright]

LOL - That poor old cat is getting skinned a hundred different ways here.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[kjv1611]

M
I
C
K
E
Y

M
O
U
S
E

Yay! [WINK]

 

[AnotherHiggins]

Wow, this thread has generated a lot of responses!?!?

I just wanted to add one little thing:
First, for the record, I, like firefytr, simply use
[Ctrl] + [C] then [Alt] + [E], , [V] as a keyboard shortcut. It sounds cumbersome, but you get used to it.

I also agree that selection.value = selection.value is the best way to go for code. I have that mapped to a button on a custom toolbar. It is useful not only for changing formulae to values, but usually works to convert those pesky imported numeric strings to actual numbers.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[kjv1611]

It is useful not only for changing formulae to values, but usually works to convert those pesky imported numeric strings to actual numbers. [/qtuoe]

Hmm, I might could really use a button for that, b/c that is QUITE an annoyance when it occurs!