View Full Version : Solved: Need to get Part# from string

frank_m

10-18-2011, 12:03 AM

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

frank_m

10-18-2011, 12:48 AM

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

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

Why not just use Data>Text to Columns with a space delimiter Frank?

frank_m

10-18-2011, 07:26 AM

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.

shrivallabha

10-18-2011, 09:08 AM

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.

mikerickson

10-18-2011, 09:43 AM

=TRIM(LEFT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",255)),255)

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

shrivallabha

10-18-2011, 10:31 AM

=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

frank_m

10-18-2011, 03:39 PM

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. :yes

----

Hi mikerickson, that works nicely :thumb

---

Thank you both for your time :friends:

mikerickson

10-18-2011, 04:01 PM

Just apply trim to the cell value.

frank_m

10-19-2011, 02:48 AM

Thanks mikerickson

I thought I had tried that, but apparently I made a typing error, as this is now a working alternative.

=IF(ISERROR(TRIM(LEFT(A2,FIND(" ",A2,1)))),A2,TRIM(LEFT(A2,FIND(" ",TRIM(A2),1))))

mikerickson

10-19-2011, 07:39 AM

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)

shrivallabha

10-19-2011, 10:04 AM

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)

frank_m

10-19-2011, 10:54 AM

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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.