ANmarS - Search results

Excel/VBA expert since Sep 1997 (21 years 2 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

5 Q & A posts tagged with LEFT

Mon
Feb 6
2012

Copy pictures from sheet to another

i have two workbooks, lets say, workbook A and Library Workbook
Workbook A is where all the code has been going, Library has no code in it. My project in Workbook A needs to look up and pull a worksheet out of Library Workbook - just a direct copy. Simple enough in general, only one problem. Most of the sheets in Library Workbook have images embedded in them that dont get copied when doing a direct Workbooks(Library)Cells.Copy
i can get all of the formatting, column widths, and values/formulas, but not the images
now, there is one thing i havent tried that I think will work, but it has drawbacks...i could copy the entire sheet, not just the cells, but the sheet itself and put it in Workbook A. I think that this would work in theory, but Im reluctant to try it for one reason
I have embedded sheet code in Workbook A in the sheet that I want to be the destination for the copied library contents. The sheet code is event-based, and Id like it to stay in the code. So I was trying to paste the library contents into this sheet to retain the sheet code, where if I paste the entire sheet directly from the library, I dont get to keep the sheet code, because the library, by definition, is supposed to be (vba free)

so...Ive been chasing two different possible solutions, neither one with much luck yet.
(1) Learn how to copy cells from a worksheet with the images included
i know you can use vba to copy images, but the sheets have varying numbers of images, so theres no good way to programatically handle all sheets
shapes(1).copy, ...
might be more than one image


Answer

you still can use shapes.count and start a loop, did you tried that?
however, there might be a downside, when you copy and paste, they will all be over each other, but you can override it by using:
Sheet1.shapes(x).left = Sheet2.shapes(x).left
sheet1.shapes(x).top = sheet2.shapes(x).top
assuming copying shape from Sheet2 to Sheet1
does that make sense?

Monday 2/6/2012
Mon
Apr 12
2010

How do I find an 10 character alphanumeric string in a Excel spreadsheet cell with 70+ characters?

I need to find a 10 digit character string that is in a cell in Column G that has 70 plus characters. It can be located anywhere. The 10 character string will have these unique attributes:

* 10 characters long
* 1st two characters will be alpha (can upper or lower case)
* last 8 characters will be numeric

The following formula provided by this forum gives me a TRUE return if the this string is the only data in the cell, starting in position 1.

=AND(LEN(F2)=10, CODE(LEFT(F2,1))>64, CODE(MID(F2,2,1))>64, ISNUMBER(VALUE(RIGHT(F2,8))))

How can this formula be modified to:

1.) Allow me to find the string anywhere in the text
2.) Place the 10 digit string into Column H

Thank you.
Your assistance is appreciated.

Mark


Answer

I would do it if I only have the file

mail it to me so that I can do what you want

Trust me, If Excel can do it, then VBAXLMan can do it

Monday 4/12/2010 5:10:35 AM
Mon
Apr 13
2009

Separate English and Traditional Chinese (excel file) into 2 columns without special characters (; or : or *)?

Hello there:

Hope u can help me...

I have a huge list of Buddhist terms (20,000) in Traditional Chinese, together with the English definition for each of the 20,000 entries. Problem: all of the terms are listed in a single column, with the english definition coming before every Traditional Chinese concept: what I try to mean with this is that some english definitions involve more than one single word (some definitions have 23 words as a whole), and the same concept, but in Chinese, might involve more than just one character (up to 7).

I cannot use a "space" to separate the text into different columns, because of the amentioned reason, there are not special characters that separate the English of the Chinese (such as ; or : or *), its all like the following sample:


9-11 am ??
a (walled) city ?
a bed ??
a day without work is a day without food ????????
a heap ?
a ko?i of nayutas ?????
a man ??
a thread, a butt ????
a wild fellow ???

like this, all the way up to 20,000

So, do u know of any way I can separate the whole text into different columns, based on the used script (Chinese and English)?

I asked some Computer science teachers at a local university: they suggested to create a macro that differentiates the script in use according to ASCII (english letters come with very low numbers, while Traditional Chinese must involve really big numbers, or so they said), but, I confess that my knowledge of computing is extremely low (main reason Im contacting u, btw...)

Hope u read this message, understand my broken english, and give me an answer...

Thanks a lot

Cheers


Answer

You don't need a macro to do that
I just fixed something like this (in pure functions) few days ago, it was about changing CamelCase to Proper, here is the file in case you are interested

Modifying that file, you can do something like this

If you have the cell in B4, paste this in E4

=IF(OR( COLUMN()-4>LEN( $B4),SUM( $D4:AC4)>0),"",IF(CODE( MID( $B4,COLUMN( )-4,1))>150,COLUMN( )-4,""))
Then copy and paste up to column AD (to get the possibilities of 26 characters)
Now in cell AE4, paste this to get the left side of the text (Before the first Chinese char)
=LEFT(B4,MAX(E4:AD4)-1)

And this in nAF4 to get the right part (after the first Chinese char)
=MID(B4,MAX( E4:AD4)+1,500)


Good luck with that, let me know if you didn't understand it or you want to edit it

VBAXLMan is here to fill your Excel needs

Monday 4/13/2009 12:09:05 PM
Sun
Jan 18
2009

What is the simplest way to extract text pattern in Excel spreadsheet?

I have a sheet of a few columns of URLs. I need to extract a text pattern (I.E. extracting an URL parameter) and then replace it with something else and place it into another link structure. What's the simplest way to do that in Microsoft Excel spreadsheet? Does Excel provide regular express?


Answer

Yes, sure
I do this all the time
Now, the link you have may look like this
http://www.file1.net/project.asp?id=89

If this link in cell A1, then paste this in B1
=LEFT(A1, SEARCH("?",A1)-1)

This will bring you the first part until the "?"

Then in another cell, say C1 type 5

Then in D1 paste this
=HYPERLINK( B1&"id="&C1, B1)

Now this link will have this result
http://www.file1.net/project.asp?id=5

So doing 6 in C2, 7 in C3, etc
Copy and paste D1 down to generate multiple hyperlinks to different locations based on your criteria

I did this as example, because you didn't give any details

try applying your criteria and see

VBAXLMan is here to feed your Excel needs in case you want more help

Sunday 1/18/2009 4:34:54 AM
Fri
Jan 16
2009

Excel - How to get the frequency of the words in a para?

The para contains 1000 words. I need to find out the the top 50 words appearing most often in the para together with the number of appearances only using excel commands - can't use VBA or any other s/w!

any help ??


Answer

The only way to do it is to convert that para into cells (For temporary)

Now, if you have that cell in A1, paste this in B1
=LEFT( A1,SEARCH( " ",A1)-1)
And this in C1
=LEN( B1)
And this in D1
=COUNTIF( B:B,B1)
And this in B2
=MID( $A$1,SUM( $C$1:C1)+2,SEARCH( " ",$A$1,SUM( C1:C1)))
and copy C1 to C2, D1 to D2

Now copy cells B2, C2 and D2 down until you find all the text you have in that para in the cells of column B

Now, the D column will give you the number of any corresponding text found in cell A1

The interesting thing, is that when you change cell A1, all of these will be changed automatically


Enjoy it, Yes, I know I am good at this, I am the VBAXLMan here

Friday 1/16/2009 6:13:55 PM

Tags

History