Excel Help and Problems

[jose34]

I'm trying to Paste a bunch of lines from an Excel Document  into  a Microsoft Word table. However whenever I paste into Word it doubles the data a bunch of times over. Any suggestions?

Please see attachments for clarification.

[etech0]

I'm trying to Paste a bunch of lines from an Excel Document  into  a Microsoft Word table. However whenever I paste into Word it doubles the data a bunch of times over. Any suggestions?

Please see attachments for clarification.

Looks like something funny is going on. Can you attach your excel file?

You could also try copying and doing Paste Values into another excel sheet and then try copying that into Word.

[aygart]

I'm trying to Paste a bunch of lines from an Excel Document  into  a Microsoft Word table. However whenever I paste into Word it doubles the data a bunch of times over. Any suggestions?

Please see attachments for clarification.

My guess is that you have some merged cells

[Yammer]

I have an Google sheet that I'm putting in name/address information. I currently have a data validation list of all the state abbreviations.

Is there a way for the person entering the data to see the full state name but once selected, the abbreviation comes up?

[gozalim]

I have excel data which I need sorted alphabetically.
Data is in hebrew
is there a way to get it to sort
יג, יד, טו, טז, יז...

(I'd try to create a unique order list, kind of 'sort array' then sort by that column, but I think that'll only work if the entire cell is the specific item match. here I need it to treat it that way 'alphabetically'-where it's the beginning of a larger string of data (e.g. טז,כ comes after יד, ח)

[etech0]

I have excel data which I need sorted alphabetically.
Data is in hebrew
is there a way to get it to sort
יג, יד, טו, טז, יז...

(I'd try to create a unique order list, kind of 'sort array' then sort by that column, but I think that'll only work if the entire cell is the specific item match. here I need it to treat it that way 'alphabetically'-where it's the beginning of a larger string of data (e.g. טז,כ comes after יד, ח)

Maybe a hidden sheet with a list of hebrew #s and regular #s? Then you could vlookup to get the # and sort by the vlookup column.

[gozalim]

Maybe a hidden sheet with a list of hebrew #s and regular #s? Then you could vlookup to get the # and sort by the vlookup column.

that's what I was referring to in the second half of my post. I've gotten stuff like that to work on full cells, not where "cell starts with..."

[etech0]

that's what I was referring to in the second half of my post. I've gotten stuff like that to work on full cells, not where "cell starts with..."

What kind of data is in your cells? Can you give a sampling?

You may still be able to use vlookup, with the last argument set to "true"

[gozalim]

What kind of data is in your cells? Can you give a sampling?

You may still be able to use vlookup, with the last argument set to "true"

see here

בראשית   ב, י
שמות   יט, ג
תהלים   קיג, ט
בראשית   ב, כא-כב
משלי   כד, ג-ד
שמות   יד, א
שמות   לה, כב
בראשית   ג, ו
בראשית   ג, טז-יט
בראשית   ג, כב-כג
שמות   לה, כב
שמות   לה, כה-כו
תהלים   קכו, ה
משלי   ג, ו
ישעיהו   נז, טז
שמות   טו, כ-כא
בראשית   ב, א-ג
שיר השירים   ג, יא


I have the 'sefer' in its own cell, sorted by custom lookup. 2nd column sorts great alphabetically except for where שמות   טו, כ-כא will be listed before שמות   יד, א

[gozalim]

What kind of data is in your cells? Can you give a sampling?

You may still be able to use vlookup, with the last argument set to "true"

I hadn't had that work for me (in the sefer cell either) though I may try again. If yes-that would work for the פרק (though not for the פסוק).
what do seforim people do?

[etech0]

see here

בראשית   ב, י
שמות   יט, ג
תהלים   קיג, ט
בראשית   ב, כא-כב
משלי   כד, ג-ד
שמות   יד, א
שמות   לה, כב
בראשית   ג, ו
בראשית   ג, טז-יט
בראשית   ג, כב-כג
שמות   לה, כב
שמות   לה, כה-כו
תהלים   קכו, ה
משלי   ג, ו
ישעיהו   נז, טז
שמות   טו, כ-כא
בראשית   ב, א-ג
שיר השירים   ג, יא


I have the 'sefer' in its own cell, sorted by custom lookup. 2nd column sorts great alphabetically except for where שמות   טו, כ-כא will be listed before שמות   יד, א

hmm, that does complicate things. you might need to use formulas to separate out the perek and pasuk.

[gozalim]

wondering about an alternate possibility: run a "replace all" before and after sorting. (e.g. all טו=יה, sort, then in reverse) considering that i  don't expect examples of the alternate word.

problem: where alternate word exists within larger word (e.g. ישעיהו). in theory searching for "whole word only" should solve 90% of the issue, but I don't see such a choice in excel (only "whole cell). and searching with space preceding won't work for where the cell begins that way.

Is there a way to search "whole world only" or a wildcard that means "no character" (kinda reverse of *)
?

[etech0]

wondering about an alternate possibility: run a "replace all" before and after sorting. (e.g. all טו=יה, sort, then in reverse) considering that i  don't expect examples of the alternate word.

problem: where alternate word exists within larger word (e.g. ישעיהו). in theory searching for "whole word only" should solve 90% of the issue, but I don't see such a choice in excel (only "whole cell). and searching with space preceding won't work for where the cell begins that way.

Is there a way to search "whole world only" or a wildcard that means "no character" (kinda reverse of *)
?

could you transfer this to google sheets? If so, regex would open up a lot of possibilities

[gozalim]

could you transfer this to google sheets? If so, regex would open up a lot of possibilities

tell me more

[etech0]

tell me more

You could use regexextract to split "בראשית   ב, י" into 3 cells, then you'd be able to do the vlookup on the ב cell and the י cell and sort.

Another option would be to use regexreplace to convert טו and טז into their equivalents, for sorting purposes.

[gozalim]

I find it interesting that gdocs has regex where excel does not (especially considering word does IINM)
here I was thinking that excel is the more robust program with gdocs being more lightweight...

[etech0]

I find it interesting that gdocs has regex where excel does not (especially considering word does IINM)
here I was thinking that excel is the more robust program with gdocs being more lightweight...

+1 it was very surprising to me as well

[gozalim]

+1 it was very surprising to me as well

am I wrong about gdocs in general?

[etech0]

am I wrong about gdocs in general?

IMO Excel is more robust, and also more accepted in the business world. Though it's possible that over time gdocs has been adding features and catching up somewhat. Personally, I turn to google sheets in 3 situations:
1) I want to collaborate with other people
2) I want to access the file from any computer (eg: at home / at work)
3) I want to use regex

[gozalim]

You could use regexextract to split "בראשית   ב, י" into 3 cells, then you'd be able to do the vlookup on the ב cell and the י cell and sort.

Another option would be to use regexreplace to convert טו and טז into their equivalents, for sorting purposes.

How does regex work with hebrew?
shouldn't

Code: [Select]

\bטו\bfind th טו in

Code: [Select]

טו, ?