Solved: Need to get Part# from string

[frank_m]

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]

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]

[Bob Phillips]

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

[frank_m]

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]

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]

=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]

=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]

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

[mikerickson]

Just apply trim to the cell value.

[frank_m]

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]

[mikerickson]

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]

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]

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