MARK,it's really really work!!!

I feel so great when i see the result that i expect for LONG time!

Million Thanks to you~! :D

Best regards,
ICE W

/2250
ICE

To get a string out of a string you need to use the 'Search' function combined with the 'Mid' function.

So to extract this: '#01-08/09' from this: '9 KAKI BUKIT ROAD 1 #01-08/09 EU NOS TECH NOLINK'
we would use:
=MID(A2,SEARCH("#",A2),SEARCH(" ",A2,SEARCH("#",A2))-SEARCH("#",A2))
Simply, it gets the position of '#' (21) and the position of the 'space' (30) after the '#' and uses the MID function to return the text starting at position 21 and extrats the number of characters (30-21 = 9)

I hope that explains it.

The process is eay(ish) if you break it down into a number of functions and then put them all together to come up with a composite function - if you lay it out like this:

Remember, these all refer to a string in cell A2
[cell B2]=SEARCH("#",A2) [this returns 21 - the position of #]
[cell C2]=SEARCH(" ",A2,SEARCH("#",A2)) [this returns 30 - the position of the space after #]
[cell D2]=MID(A2,B2,C2-B2) [this returns chars between 21 & 30]
Now, in the above function, replace all occurences of B2 with the function in cell B2 and the same for C2
[cell E2]=MID(A2,SEARCH("#",A2),SEARCH("
",A2,SEARCH("#",A2))-SEARCH("#",A2)) [this is the composite]

HTH

Mark
On Tue, Sep 15, 2009 at 2:49 AM, QT - ICE W <
qtopic-27-tHAhkTQwWtmq@quicktopic.com> wrote:

>



--
Kind Regards,

Mark Percival
07544597880
ageoconsultant@gmail.com
< replied-to message removed by QT >

Can the Make HTM macro be modified to include the formating within a cell ie where bold or a word is in a different colour

Lachele,

I'm not pretty sure what effect u want.

1)words show vertically in column:
  select the column then go 'home', inside 'alignment' select 'orientation' then 'rotate text up' or others.

2)changing one row of columns from vertical to horizontal, or vice versa:
 'Copy' those columns, right click the destination column and select 'paste special' then click on 'transpose'.

Hope these will help.

Best regards,
ICE W

Hi, I have just started a spreadsheet on excel and I am pretty useless with it. I need the columns at the top of the spreadsheet to read vertically as opposed to horizontally, please can you tell me how to do this as I am pretty stuck, thank you in advance.

email address: lob-on@live.com

Mark,

=MID(A1,FIND("#",A1)-1,5) really work for me. (-1, cos i want "#" to be captured as well)

Notice the examples i show you last time were too consistence. :D

Now i would like to show some cruel things:(fail to set parameter=5)
eg: 1) 9 KAKI BUKIT ROAD 1 #01-08/09 EU NOS TECH NOLINK
    2) 9 AIRLINE ROAD, #04-04 (OFFICE) #02-24 (WAREHOUSE), CARGO AGENT BUILDING D
    3) BLK449 #01-1721 ANG MO KIO AVE 10

Add. above consist of 2 units or characters aft "#" may > 5 characters.

My desirable formula is to capture characters [AFTER "#"; BEFORE WHITE SPACE (the following word)]

Am I too greedy?? :P
Appreciate if anyone got idea to share.

Best regards,
ICE W

Ice,

The 4 refers to how many characters to retun once the '#' has been found. To extract the '#' you would set the parameter to 1 (without the '+' bit) - it seems your characters after the '#' equal 5 so just change the 4 paramater to 5 and it will return:
24-05
06-10
04-14 etc.

HTH

Mark

On Mon, Sep 14, 2009 at 10:29 AM, QT - ICE W <
qtopic-27-tHAhkTQwWtmq@quicktopic.com> wrote:

>



--
Kind Regards,

Mark Percival
07544597880
ageoconsultant@gmail.com
< replied-to message removed by QT >

You could try this:�A;�A;Ctrl+F�A;�A;Then use replace. Replace '#' with nothing.�A;�A;You will need to select the data you wish to search in first, and make sure there are no #s you want to keep!�A;�A;Good luck�A;�A;�A;�A;�A;________________________________�A;From: QT - ICE W <qtopic-27-tHAhkTQwWtmq@quicktopic.com>�A;To: QT topic subscribers <qtopic-subs@quicktopic.com>�A;Sent: Monday, 14 September, 2009 10:29:28�A;Subject:Help with Excel functions (www.meadinkent.co.uk)�A;�A;--QT-------------------------------------------------------------�A; Reply by email or visit�A; http://www.quicktopic.com/27/H/tHAhkTQwWtmq/m2247�A;--------------------------------------------------------------- -�A;�A;Thanks Richard and Mark.�A;�A;Mark, that "4" in ur formula (previous mail), is showing the�A;position in the particular stings right?�A;�A;But my data is all jumble up, meaning not the 4th word in every�A;stings = #xx-xx.�A;�A;eg: 1) #24-05 SHENTON HOUSE, 3, SHENTON WAY SINGAPORE�A; 2) BLK3 ANG MO KIO INDUSTRIAL PARK 2A #06-10 ANG MO KIO TECH�A;1�A; 3) 9 AIRLINE ROAD, #04-14 CARGO AGENTS BUILDING DBOX 538�A;�A;is there a formula to extract "#" only, regardless of the�A;position in stings??�A;�A;Best regards,�A;ICE W�A;_________________________________________________________________�A;To unsubscribe: http://www.quicktopic.com/27/X/tHAhkTQwWtmq�A;Start your own topic in 20 seconds: http://www.quicktopic.com |QT�A;�A;�A;

Thanks Richard and Mark.

Mark, that "4" in ur formula (previous mail), is showing the position in the particular stings right?

But my data is all jumble up, meaning not the 4th word in every stings = #xx-xx.

eg: 1) #24-05 SHENTON HOUSE, 3, SHENTON WAY SINGAPORE
    2) BLK3 ANG MO KIO INDUSTRIAL PARK 2A #06-10 ANG MO KIO TECH 1
    3) 9 AIRLINE ROAD, #04-14 CARGO AGENTS BUILDING DBOX 538

is there a formula to extract "#" only, regardless of the position in stings??

Best regards,
ICE W

I HAVE POOR EYESIGHT AND USE EXCELL HEADER/FOOTER AND THE FONT SIZE IS 10, TO SMALL TO SEE AND HAVE TO RAISE IT TO 16 AND SET AS DEFAULT
OTHERWISE I HAVE TO RESET IT 3 TIMES, EVERYTIME IS USE IT.
HOW DO I SET IT AT. 16 AND SET IT TO BE THE DEFAULT,
HELP PLEASE HELP A ALMOST BLIND GUY OUT

Hello Ice,�A;�A;=if(left(A1,1) = "#", do this, otherwise do this)�A;�A;Or something similar would do it. I'm not quite sure if the syntax is right but you get the idea.�A;�A;�A;�A;�A;�A;�A;________________________________�A;From: QT - ICE W <qtopic-27-tHAhkTQwWtmq@quicktopic.com>�A;To: QT topic subscribers <qtopic-subs@quicktopic.com>�A;Sent: Thursday, 10 September, 2009 10:16:47�A;Subject:Help with Excel functions (www.meadinkent.co.uk)�A;�A;--QT-------------------------------------------------------------�A; Reply by email or visit�A; http://www.quicktopic.com/27/H/tHAhkTQwWtmq/m2242�A;--------------------------------------------------------------- -�A;�A;Dear all,�A;�A;i'm trying to arrange my address nicely,�A;eg:unit,blk,street,postal code..�A;is that a way to extract out the phrase start with "#" or "blk"�A;smt like that into another column?�A;i tried a lot of formula like MID(), VALUE(),etc. but all fail.�A;�A;p/s:i got few hundred thousand of address and unit,blk,street...�A;all jumble up into a sting.�A;�A;Greatly appreciate for any help or idea.�A;�A;Best regards,�A;Ice W�A;_________________________________________________________________�A;To unsubscribe: http://www.quicktopic.com/27/X/tHAhkTQwWtmq�A;Start your own topic in 20 seconds: http://www.quicktopic.com |QT�A;�A;�A;

Ice,

The trick when extracting strings is to combine functions so that you can find a particular string:

Use FIND with MID to search for the string and then use their position to extract the characters you want:

So if we take the address '4 Hoffman House #East Street AB1 2CD'
(fictitious) and want to extract the 'East' part we would use:
=MID(A3,FIND("#",A3)+1,4) - where A3 contains the address string, FIND("#",A3)+1 gets the position of '#', adds 1 (because we don't want the '#') and 4 is the number of characters to return.
You can build up these queries using other functions.

Hope that helps.

M

On Thu, Sep 10, 2009 at 3:34 AM, QT - ICE W <
qtopic-27-tHAhkTQwWtmq@quicktopic.com> wrote:

>



--
Kind Regards,

Mark Percival
07544597880
ageoconsultant@gmail.com
< replied-to message removed by QT >

Dear meadinkent,

i'm trying to add a func to show the following:
=SUMPRODUCT(($B4=SNames)*(SFrom<=C$3)*(STo>=C$3))+IF(WEEKDAY(C$3,2)>5,2,0)
i need to add a function help show on the annual leave planner A for absent, W for wkends, but how do i show for FL, which will stand for flexible leave, i know the sheets are connected but how do i implment this.

thanks

this is from ur annual planner document that i purchased yesterday.

Dear all,

i'm trying to arrange my address nicely, eg:unit,blk,street,postal code..
is that a way to extract out the phrase start with "#" or "blk" smt like that into another column?
i tried a lot of formula like MID(), VALUE(),etc. but all fail.

p/s:i got few hundred thousand of address and unit,blk,street... all jumble up into a sting.

Greatly appreciate for any help or idea.

Best regards,
Ice W

Hello

Have been copying from one worksheet so that when i change a number or text on the first sheet it changes to the same on the other worksheets.
somehow i have done something and now the formula shows in the cell/row instead of copying from the fist worksheet.
For example:
='NAME OF FIRST WORKSHEET'!S4
This should then give me the same info that is in the cell/row on the first worksheet but now all i see is the above formula.
What have i done wrong
Really would appreciate yr help!
Rgds

Hi,

I am using the "Office annual leave planner", have it slightly modified. Was wondering I how can modify it to show Training dates in addition to the vacation dates.
Thanks in advance for any help.