Consulting

Results 1 to 13 of 13

Thread: Solved: Need to get Part# from string

  1. #1
    VBAX Expert
    Joined
    Sep 2010
    Posts
    604
    Location

    Solved: Need to get Part# from string

    I have a lot of string's like below in Column A
    I need the left portion of the string (the Part#) put into column B
    I thought of using the split function but I don't know how to deal with the varying length of the space.
    A formula would be great if someone would be so kind as to supply me with that.

    Thanks

    (The underscore represents a space, as I posting here strips out extra spaces and I need them shown to demonstrate my requirement)

    8-1677-101-NI _____ 100 PCS R.T.S
    805-0872-1 __ 110 PCS R.T.S
    8-2719-2-U
    8-2188 _______ WE HAVE 480 PCS
    8-2431-U NO STOCK
    8-2624 ____________ 1,240 PCS R.T.S
    8-2119-U WE HAVE 400 PCS
    8-0853-102-NI WE HAVE 800 PCS

  2. #2
    VBAX Expert
    Joined
    Sep 2010
    Posts
    604
    Location
    Turns out that I can use the Split function after all, as the space width wasn't an issue as I thought it would be.

    Sorry if I wasted anyones time
    [vba]
    Public Sub Process_PN_String()
    Dim strArr() As String
    Dim MyStr As String
    Dim LastRow As Long
    Dim rng As Range
    Dim cell As Range
    Application.ScreenUpdating = False

    With ActiveSheet

    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    Set rng = .Range(.Cells(1, 1), .Cells(LastRow, 1))

    For Each cell In rng

    MyStr = Trim(cell.Value)
    strArr = Split(MyStr, Space(1))
    On Error Resume Next ' to avoid error if row is empty

    cell.Offset(0, 1).Value = Trim(strArr(0))

    Next cell
    End With

    Application.ScreenUpdating = True
    End Sub
    [/vba]

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,149
    Location
    Why not just use Data>Text to Columns with a space delimiter Frank?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    VBAX Expert
    Joined
    Sep 2010
    Posts
    604
    Location
    HI Bob,

    Well firstly I'm still a dummy and wasn't famliar with that, but now that I try it, I'm getting a result that I'm not fond of. - For one: if there is a leading space, the PN gets shifted over one Column from the PN's that do not have a leading space and ALSO: each piece of data gets its own column at every space, when I need the original string all in one column. (Even the PN I want left in the original string; only a copy of the PN in Column B)

    The leading space I do know how to get rid of before using text to columns to solve that issue, but the seperating at every space and to leave the original string intact, I don't think controling that would be possible..

    Thanks though for the suggestion. Hopefully I'll remember that in other situations.

  5. #5
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    Posting it late but this formula can be used (at least with samples given). If you are using 2007+ then this formula can be shortened:
    =IF(ISERROR(TRIM(LEFT(A2,FIND(" ",A2,1)))),A2,TRIM(LEFT(A2,FIND(" ",A2,1))))
    Edit: Considering your data starts from Cell A2.
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  6. #6
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,776
    =TRIM(LEFT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",255)),255)


    by the way, =FIND(" ", A2&" ", 1) will never return an error (unless A2 has an error)

  7. #7
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    Quote Originally Posted by mikerickson
    =TRIM(LEFT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",255)),255)


    by the way, =FIND(" ", A2&" ", 1) will never return an error (unless A2 has an error)
    Try the third entry: 8-2719-2-U with my construct!
    Edit: Oops! you were explaining a different construct...sorry
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  8. #8
    VBAX Expert
    Joined
    Sep 2010
    Posts
    604
    Location
    Hi Shrivallabha,

    That works well as long as there is not a leading space in the string.
    Just for learning sake only, is there a way to trim that in your formula?

    Admitedly there are no leading spaces in my examples.

    ----

    Hi mikerickson, that works nicely
    ---

    Thank you both for your time

  9. #9
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,776
    Just apply trim to the cell value.

  10. #10
    VBAX Expert
    Joined
    Sep 2010
    Posts
    604
    Location
    Thanks mikerickson

    I thought I had tried that, but apparently I made a typing error, as this is now a working alternative.
    [vba]
    =IF(ISERROR(TRIM(LEFT(A2,FIND(" ",A2,1)))),A2,TRIM(LEFT(A2,FIND(" ",TRIM(A2),1))))
    [/vba]

  11. #11
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,776
    You would have to apply TRIM to all occurances of A2 within the formula.
    But, the ISERROR construction is not needed

    =LEFT(TRIM(A2), FIND(" ", TRIM(A2)&" ", 1)-1)

  12. #12
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    The other day I'd used ISERROR syntax on MrExcel and then Peter SSs had explained the above construct.

    But I keep on forgetting things. Mike, thanks for the reminder.

    And yes, if you are using Excel 2007+ then the above formula can be reduced to:
    =IFERROR(TRIM(LEFT(A2,FIND(" ",A2,1))),A2)
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  13. #13
    VBAX Expert
    Joined
    Sep 2010
    Posts
    604
    Location
    Quote Originally Posted by mikerickson
    You would have to apply TRIM to all occurances of A2 within the formula.
    Hi Mike,

    Yes I thought that, but for some reason it does work when only adding it at the end.
    - so being the total amatuer that I am, I inappropriately guessed that shorter might be better.

    I will indeed take heed in your various advice's.

    Thanks again to both you guys

Posting Permissions

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