+ Reply to Thread
Results 1 to 11 of 11

extracting info from a column not working completely

  1. #1
    Registered User
    Join Date
    08-12-2004
    Location
    Mendoza, Argentina
    MS-Off Ver
    Office Professional 2003
    Posts
    89

    extracting info from a column not working completely

    Hi all,
    I have an excel worksheet in which column H has info such as "house 12 in Main St, Town".

    I am trying to extract the first part "house 12 in" to column K. This works fine.

    However, column H has not removed the "n " and the space after "n".

    The code I am using is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I am just using 2 rows as a test - when I have the code working correctly, I will use it for several thousand rows.

    Has anyone any idea how to correct this error, please.

    Many thanks in advance.
    Last edited by thadacto; 03-20-2017 at 08:23 PM.

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: extracting info from a column not working completely

    how about this:
    with the test address in cell A1
    I have shown the excel formula and converted it to VBA

    Please Login or Register  to view this content.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Valued Forum Contributor Maudibe's Avatar
    Join Date
    12-21-2016
    Location
    USA
    MS-Off Ver
    2010-2013
    Posts
    326

    Re: extracting info from a column not working completely

    if Kev's code does not work for you, consider using the Split function.

    Please Login or Register  to view this content.
    HTH,
    Maud

  4. #4
    Registered User
    Join Date
    08-12-2004
    Location
    Mendoza, Argentina
    MS-Off Ver
    Office Professional 2003
    Posts
    89

    Re: extracting info from a column not working completely

    Thanks for the help, Kev. However, a message box is not exactly what I was looking for.

    I have in excess of 3,000 rows with the full address ("house 12 in Main St, Town") where the "house 12 in" has to be extracted to another column. the result should be in (say), column G [house 12 in] leaving column H with [main St, Town].

    Therefore,I can then sort the whole worksheet by street names in alphabetical order. at present, I cannot sort as I wish.

    Again many thanks for your help and I will keep your code for possible use at a later date.

  5. #5
    Registered User
    Join Date
    08-12-2004
    Location
    Mendoza, Argentina
    MS-Off Ver
    Office Professional 2003
    Posts
    89

    Re: extracting info from a column not working completely

    Thanks for your input, Maudibe.

    Like Kev's input, your code does not do what I want - it deletes the "house 5 in" instead of it extracting and putting it in another column.

    Again, thanks for your help.

    However, I have solved , in part, how to resolve my issue of the original code leaving "n " in the main address column.
    After running the main code, I used the inbuilt "replace" (Edit>Replace) where I just replaced the "n " with nothing. This has worked, but not using it in the code.

    Anyway, problem solved.

    Thanks again for your and Kev's help.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: extracting info from a column not working completely

    Trh change to
    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: extracting info from a column not working completely

    The MsgBox was just a way for you to see the result based on the one cell in the example to see if it gave you the correct result
    I have included the "in" within column G value

    Assumes addresses are in columnA starting at row 2

    This is how you build it into your code:
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    08-12-2004
    Location
    Mendoza, Argentina
    MS-Off Ver
    Office Professional 2003
    Posts
    89

    Re: extracting info from a column not working completely

    Hi Jindon, many thanks for your suggestion - it worked perfectly.
    Unfortunately, as my VBA skills are not very good (and as I'm 72 years old, I am not very good at learning the VBA language) I will see if I can understand the difference between the two lines of code.

    Again, many thanks for your help.

  9. #9
    Registered User
    Join Date
    08-12-2004
    Location
    Mendoza, Argentina
    MS-Off Ver
    Office Professional 2003
    Posts
    89

    Re: extracting info from a column not working completely

    Hi again, Kev.
    Yes, your code works perfectly. So anybody trying to do the same will have several choices to choose from.

    Again, many thanks for all the work you put into the code making.

  10. #10
    Valued Forum Contributor Maudibe's Avatar
    Join Date
    12-21-2016
    Location
    USA
    MS-Off Ver
    2010-2013
    Posts
    326

    Re: extracting info from a column not working completely

    Like Kev's input, your code does not do what I want - it deletes the "house 5 in" instead of it extracting and putting it in another column.

    Thadacto,

    Just as an FYI, the code I wrote splits the string leaving the street and town in column H and moving the "house xx in" to column k. Nothing is deleted.

    Maud

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: extracting info from a column not working completely

    Quote Originally Posted by thadacto View Post
    Hi Jindon, many thanks for your suggestion - it worked perfectly.
    Unfortunately, as my VBA skills are not very good (and as I'm 72 years old, I am not very good at learning the VBA language) I will see if I can understand the difference between the two lines of code.

    Again, many thanks for your help.
    The difference is only "Find("n ",H2:H3)+2".
    Find function returns the position in the whole string.
    i,e
    house 12 in Main St, Town
    Find("n ",H2) = 10, so Mid(H2,10) include find string that is "n ".
    If you add length of find string that is "n " =2, it only extracts after "n ".

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] extracting info from one cell based on info from other cells
    By maximelling in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-16-2017, 04:11 AM
  2. If column is populated, copy and paste info. into new workbook(keeping other column info.)
    By mary.gallagher in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-24-2013, 02:56 PM
  3. Extracting Column Info
    By BRamsundar in forum Excel General
    Replies: 1
    Last Post: 02-06-2013, 03:04 PM
  4. ActiveX ComboBox Completely Stops Working
    By daedelous00 in forum Excel General
    Replies: 1
    Last Post: 01-25-2013, 11:00 AM
  5. [SOLVED] Formula's not working Properly, and acting completely bizarre!
    By Sky188 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-04-2012, 01:05 PM
  6. Completely freeze column
    By ABSTRAKTUS in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-18-2012, 03:13 AM
  7. Excel 2007 : SUMIFS not working completely
    By bjstaten in forum Excel General
    Replies: 2
    Last Post: 01-14-2012, 12:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1