Copy specific numbers/text in a cell to another

[htou]

Hi

I'm looking for any tips, tricks, solutions of coping a specific ‘string’ of text contained in a cell into another cell in the same sheet.

Ive googled searched which resulted in one post post in this forum and searched in this forum for answers however I’ve struggled to apply the solutions offered to my dilemma. Its like the questions in those post are kinda like what im asking now but not really (if that makes sense) plus I'm a VBA noob.

Sample data with original data and what im looking for below. The data I want copied is the lot number and plan number in Column B i.e. Lot 5021 on plan PH360… [italics = the data I want copied] resulting in 5021PH360 in an adjoining cell.

I have about 300 entries in a sheet, one column contains the lot/plan numbers amongst other text.

Thank you

ORIGINAL

Excel 2010
	A	B
1	Area No.	Area Description
2		Lot 5021 on Plan PH360 Rewan
3		Lot 1 on Plan SP105325 - Consuelo
4		Lot 1 on Plan SP187949 Albinia
5		That part of Lot 6 on Plan WT800342 that is within the external boundary - Upper Dawson
6		That part of Lot 13 on Plan DSN725 that is within the external boundary - Orion
7		That part of Lots 1 and 2 on Plan SP187945 that is within the external boundary - Albinia
Sheet1

COPY OF LOT/PLAN NUMBER IN ADJOINING CELL

Excel 2010
	A	B	C
1	Area No.	Area Description
2		Lot 5021 on Plan PH360 Rewan	5021PH360
3		Lot 1 on Plan SP105325 - Consuelo	1SP105325
4		Lot 1 on Plan SP187949 Albinia	1SP187949
5		That part of Lot 6 on Plan WT800342 that is within the external boundary - Upper Dawson	6WT800342
6		That part of Lot 13 on Plan DSN725 that is within the external boundary - Orion	13DSN725
7		That part of Lots 1 and 2 on Plan SP187945 that is within the external boundary - Albinia	1SP187945 2SP187945
Sheet1

 

[Coddington]

This method looks pretty hacked, but the advantage is you don't even need VBA. I've added 5 columns of formulas ...
C2 contains =LEFT(E2,FIND(" ",E2)-1)&LEFT(G2,FIND(" ",G2)-1)
D2 contains =RIGHT(B2,LEN(B2)-FIND("Lot",B2)-2)
E2 contains =RIGHT(D2,LEN(D2)-FIND(" ",D2))
F2 contains =RIGHT(B2,LEN(B2)-FIND("Plan",B2)-3)
G2 contains =RIGHT(F2,LEN(F2)-FIND(" ",F2))

Put those formulas in and copy them to each row.
It's not pretty and it may not be the best solution, but it works. One problem with a task like this is anomalies arise whenever the text varies slightly. Good luck. If you want a VBA solution, just say so.

 

[htou]

Thanks Coddington, it maybe ugly but it works for most of the data except where i have two Lot numbers in the cell - see sample data above cell B7. The results in this situation comes back with the first lot number only and then the plan number.

It doesnt pick up the second lot number and associate it with the plan number so might need to fix these up manually. Curious though, are you suggesting you have a VBA solution? If so then im interested.

 

[Rick Rothstein]

ORIGINAL
Excel 2010

	A	B
Area No.	Area Description
Lot 5021 on Plan PH360 – Rewan
Lot 1 on Plan SP105325 - Consuelo
Lot 1 on Plan SP187949 – Albinia
That part of Lot 6 on Plan WT800342 that is within the external boundary - Upper Dawson
That part of Lot 13 on Plan DSN725 that is within the external boundary - Orion
That part of Lots 1 and 2 on Plan SP187945 that is within the external boundary - Albinia

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1
​

The example in cell B7 raises the question of what the cell would look like (not the answer cell, but the data cell itself) if there were 3, 4 and/or more individual Lot numbers? If you want us to give you a way to pull the lot numbers out, you have to tell us all the ways they could look so we can figure out what text to keep and what text not to keep.

 

[htou]

Good point Rick,

From what i can tell there is no 3rd or 4th etc lot number in the cell so there is only two numbers and always separated by the word "and" i.e. That part of Lots 1 and 2 on Plan SP187945 that is within the...

Also, the lot/plan number is not always in the same spot in the cell. What i mean is that the lot/plan number might be at the end of the text i.e. Arcadia valley external boundary - the following lot description Lot 3 on Plan TR21 is within. or at the start like B7 in the sample data.

What is always common in each cell however is the word Lot and Plan.

 

[Rick Rothstein]

Good point Rick,

From what i can tell there is no 3rd or 4th etc lot number in the cell so there is only two numbers and always separated by the word "and" i.e. That part of Lots 1 and 2 on Plan SP187945 that is within the...

Also, the lot/plan number is not always in the same spot in the cell. What i mean is that the lot/plan number might be at the end of the text i.e. Arcadia valley external boundary - the following lot description Lot 3 on Plan TR21 is within. or at the start like B7 in the sample data.

What is always common in each cell however is the word Lot and Plan.

Give this UDF (user defined function) a try...

Function LotPlan(Text As String) As String
  Dim X As Long, TempText As String, Plan As String, Lots() As String
  TempText = Split(Text, "Plan ")(1)
  Plan = Left(TempText, InStr(TempText, " ") - 1)
  TempText = Trim(Split(Text, "Lot")(1))
  Lots = Split(TempText, " and ")
  If UBound(Lots) = 0 Then
    LotPlan = Split(Lots(0))(0) & Plan
  Else
    LotPlan = Split(Lots(1))(0) & Plan
    Lots = Split(Lots(0))
    LotPlan = Lots(UBound(Lots)) & Plan & vbLf & LotPlan
  End If
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use LotPlan just like it was a built-in Excel function. For example,

=LotPlan(B2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Coddington]

Good job Rick.

And if you can do it on a spreadsheet, there is always a VBA solution, I think.

 

[Rick Rothstein]

Good job Rick.

Thanks!

And if you can do it on a spreadsheet, there is always a VBA solution, I think.

I think that is backwards... you can always do it in VBA whether or not it can be done using formulas on a worksheet.

 

[htou]

Sorry guys for the delayed reply, i only got to test the UDF tonight on the sheet and it worked fantastically.

Thank you Rick for your help and to Coddington for the interim solution