Ask me anything about Microsoft Excel - Page 119 - Other Topics Forum - Discuss Pop Culture, Arts and Entertainment

I have a column of 600 part descriptions in Chinese. How do I mass translate the whole column to English? Preferably in a new column next to the original column.

I have found the Translate command in the Review Tab. I can view an English translation but it doesn't change cell contents.

Quote

01-28-2021 , 03:23 PM

#2953

marknfw

Gaming Commissioner

Join Date: May 2010 Posts: 13,516

I'm having trouble with the "undo" function. When I have multiple files open and click undo it undoes whatever I did last no matter what file it was, not just on the file I clicked on. I've tried opening separate excel windows prior to opening files and it still does it. Is there just no way to have it work only on whatever page I'm clicking on?

Edit: NVM, figured out to use alt on the Start menu link and it opens separate instances.

Last edited by marknfw; 01-28-2021 at 03:33 PM.

Quote

02-03-2021 , 10:06 AM

#2954

Parsons Grinder

veteran

Join Date: Aug 2014 Posts: 2,372

Hi guys, hoping someone can help me here!

I'm looking for a way to ensure when I click on a link in a spreadsheet, when it takes me to the target cell it automatically smaps to the top left hand side of the screen.

I have seen solutions such as:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
ActiveWindow.ScrollColumn = ActiveCell.Column
ActiveWindow.ScrollRow = ActiveCell.Row
End Sub

But unfortunately I don't really know what this means. I assume it is to do with VBA.

Not all of my target cells are A1 either, although they are all in column A.

If anyone can help me it will be greatly appreciated!

Quote

02-07-2021 , 03:15 PM

#2955

txdome

Carpal \'Tunnel

Join Date: Mar 2007 Posts: 18,111

What am I looking at and why is this sponsored by The Hill?

[IMG]

[/IMG]

Quote

02-07-2021 , 05:14 PM

#2956

sumey

veteran

Join Date: Oct 2009 Posts: 2,815

Quote:

Originally Posted by Parsons Grinder

Press Alt+F11 to open the VBA window.

Double click on the sheet containing your hyperlink(s) under Microsoft Excel Objects (top left).

In the main panel paste the code you found.

Close the VBA window.

Last edited by sumey; 02-07-2021 at 05:24 PM.

Quote

02-07-2021 , 06:28 PM

#2957

xander biscuits

watching a movie

Join Date: Oct 2009 Posts: 39,888

Quote:

Originally Posted by kerowo

You know, just use a function.

what sort of function?

Quote

02-11-2021 , 09:57 PM

#2958

sumey

veteran

Join Date: Oct 2009 Posts: 2,815

Quote:

Originally Posted by xander biscuits

what sort of function?

Eagerly awaiting the response in early 2031.

Quote

04-18-2021 , 04:33 PM

#2959

topspinner

old hand

Join Date: May 2004 Posts: 1,336

Simple request that I used to know how to do. This is in one cell and i want to total the two numbers W 7-2. Thank you .

Quote

04-18-2021 , 07:18 PM

#2960

Yeti

Abominable

Join Date: Jun 2004 Posts: 23,208

not sure if you meant to post a screenshot?

just click on the cell you want the value to be in, type =SUM( and then go click the cells you want to sum. you can do this by either clicking and dragging over the range, or pressing CTRL and clicking individual cells, or a combination of both.

Quote

04-19-2021 , 07:10 AM

#2961

topspinner

old hand

Join Date: May 2004 Posts: 1,336

Thanks for the reply, but I thank you misunderstood what I was asking. the data is all in one cell and I want to add the two number 7 and 2 together. It is data that I have pulled from a baseball reference, and I want to get the total of the runs scored in the game.

W 7-2

Quote

04-19-2021 , 07:16 AM

#2962

Yeti

Abominable

Join Date: Jun 2004 Posts: 23,208

=SUM(MID(A1,3,1),MID(A1,5,1))

edit - er i assume either value can be double figures, so use this:

=SUM(MID(A1,FIND(" ",A1)+1,FIND("-",A1)-FIND(" ",A1)-1),MID(A1,FIND("-",A1)+1,2))

(it's probably laughably clunky but works)

Last edited by Yeti; 04-19-2021 at 07:35 AM.

Quote

04-19-2021 , 07:46 AM

#2963

topspinner

old hand

Join Date: May 2004 Posts: 1,336

Thanks Yeti, that worked

Quote

04-20-2021 , 07:25 AM

#2964

sixfour

should be called sevenfour

Join Date: Dec 2005 Posts: 75,201

If you're not adding more data then this is simple split by delimiter/position abuse then go A1+b1 etc. If you are, it's just as easy to do the same in Power Query to dump your source into a new table.

Quote

04-20-2021 , 07:56 AM

#2965

rickroll

temp-banned

Join Date: Aug 2011 Posts: 25,797

Quote:

Originally Posted by sixfour

+1 to power query over traditional import

Quote

05-05-2021 , 10:49 PM

#2966

The Apex

Pooh-Bah

Join Date: Nov 2011 Posts: 3,603

Original cell contained 2s2h2d. I deleted "s" and replaced with 2660 and hit alt x, replaced "h" with 2665 alt x, replaced "d" with 2666 alt x. This replaced the letters with corresponding suits which I changed font color for 4 color deck. How can I apply this process to entire column?

Thank you

Quote

05-05-2021 , 10:58 PM

#2967

rickroll

temp-banned

Join Date: Aug 2011 Posts: 25,797

i don't understand how you used icons but if you used the search and replace feature then you can simply highlight the entire area and hit the "replace all" button

just be careful and make sure there's no situations where it could replace stuff you don't want replaced

Quote

05-06-2021 , 11:14 PM

#2968

The Apex

Pooh-Bah

Join Date: Nov 2011 Posts: 3,603

Quote:

Originally Posted by rickroll

I used the icons by manually inputting them into the cell. I was able to change all the letters into suits but unfortunately they are all black. Are you aware of a replace formula I can use to apply the color changes to the entire column? I changed the first cell myself by highlighting the suits and selecting color as an example.

Quote

05-06-2021 , 11:41 PM

#2969

rickroll

temp-banned

Join Date: Aug 2011 Posts: 25,797

oh i think i get it

1 highlight selected region
2 go to conditional formatting
3 if icon then x color

should do the trick

is this for solver results?

Quote

05-07-2021 , 09:34 PM

#2970

The Apex

Pooh-Bah

Join Date: Nov 2011 Posts: 3,603

Quote:

Originally Posted by rickroll

oh i think i get it

1 highlight selected region
2 go to conditional formatting
3 if icon then x color

should do the trick

is this for solver results?

Yes it's for solver results. I was only able to get it to change the font color for the entire cell not just the one suit. Tried using find and replace and same thing. If anyone can make an add-in for this I will buy it, PM please.

Quote

05-07-2021 , 09:57 PM

#2971

The Apex

Pooh-Bah

Join Date: Nov 2011 Posts: 3,603

Found a VBA code for this purpose but when I insert the suit character it doesn't accept it in the module.

Quote

05-07-2021 , 10:03 PM

#2972

rickroll

temp-banned

Join Date: Aug 2011 Posts: 25,797

vba is the devil, way too frustrating for me to ever deal with all the errors, i have dipped my toes in there a few times and always leave frustrated and angry