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 >

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;

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

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

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;

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 >

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

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

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

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

/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 >

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

I ASKED FOR HELP WITH THE FONTS SIZE OF EXCELL FOOTER/HEARDER
HOW DO I CHANGE THE DEFAULT TO 16.
REGRETTABLE NO ONE WAS KIND ENOUGHT TO ANSWER IT
PLEASE I AM VISUALLY IMPAIRED. THERE HAS GOT TO BE SOME GEEKY DUDE THAT CAN HELP ME
KEVIN

KEVIN,

I'M NOT SURE IT IS THE FACT THAT NO ONE IS 'KIND ENOUGH' BUT THE FACT IS - I HAVE LOOKED AT A LOT OF TRAINING MATERIAL, TECH SITES ETC. AND HAVE NOT FOUND A WAY TO DO THIS SO I'M NOT SURE IT CAN BE DONE UNLESS SOMEONE CAN WRITE SOME CODE.

I KNOW THAT ISN'T MUCH HELP BUT IT SEEMS TO BE A GAP IN MS EXCEL.
MARK.

On Sat, Sep 19, 2009 at 2:08 AM, QT - KEVIN MENARD <
qtopic-27-tHAhkTQwWtmq@quicktopic.com> wrote:

>



--
Kind Regards,

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

I need to create a summary of content in the first worksheet that list down all the names of worksheet tabs within the same file. How do I this using the excel function? Can someone help to advice?

/m2258
Grace,
Try this macro - don't remember where I got it but I think it does what you want to do.

Sub ListingTabNames()
Dim Ws As Worksheet, Wb As Workbook, R As Range, I As Integer
Set Wb = ActiveWorkbook
Set R = ActiveSheet.Range("a1")
I = 1
For Each Ws In Wb.Worksheets
R.Cells(I, 1) = Ws.Name
I = I + 1
Next Ws
End Sub

Hope this helps....