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

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]

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

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.

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:

Quote:

---

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

---

Edit: Considering your data starts from Cell A2.

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


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

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

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:

Just apply trim to the cell value.

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]

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)

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:

Quote:

---

=IFERROR(TRIM(LEFT(A2,FIND(" ",A2,1))),A2)

---

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