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
Bob Phillips
10-18-2011, 01:23 AM
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 © 2024 vBulletin Solutions Inc. All rights reserved.