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

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.


(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-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

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

10-18-2011, 01:23 AM
Why not just use Data>Text to Columns with a space delimiter Frank?

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.

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.

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)

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

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:

10-18-2011, 04:01 PM
Just apply trim to the cell value.

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.


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)

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:


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