0

I have this code below, but instead of print the contents of A1 (i.e. CELL(""contents"", A1)), I would like the leftmost characters of A1 before the first space. Please advise.

With Workbooks("Book1").Worksheets("Sheet2").Range("C1:C" & i)
.Formula = "=IF((IF((CONCATENATE(IF(LEFT(TRIM(A1),1)=""R"",""TRUE"",""FALSE""), ISNUMBER(VALUE(MID(A1,2,1)))))=""TRUETRUE"",1,2))=1,**CELL(""contents"", A1)**,CELL(""contents"", B1))"
.Value = .Value
End With
BigBen
  • 46,229
  • 7
  • 24
  • 40
user8107531
  • 15
  • 5
  • 1
    could you maybe show us example strings and how you'd like them to end up formatted? – Marcucciboy2 Sep 10 '18 at 16:21
  • 1
    leftmost characters before a space would be `LEFT(A1, find(" ", A1, 1)-1)`. I haven't had enough coffee yet to figure out where to stick that in that monster formula though. I'm guessing delete everything between the `**` and stick `LEFT(A1, find("" "", A1, 1)-1)` in its place. – JNevill Sep 10 '18 at 16:21
  • It worked. Thanks a lot @JNevill – user8107531 Sep 10 '18 at 16:28
  • Wonderful! That's great news :) – JNevill Sep 10 '18 at 16:28
  • That formula could use a helper column or two... – Mathieu Guindon Sep 10 '18 at 16:31
  • Hey @JNevill, could you maybe advise on a way to improve the code to extract the leftmost characters until i get any character but a number. So basically, If I have "R1234- Description" or "R1234 - Description", or "R1234:Description"..For all of these, I would like to extract only the Rxxx where x corresponds to a number only – user8107531 Sep 14 '18 at 17:20
  • There will be no pretty way to do that through a formula. You'll have to write a UDF to handle it. Using [regex](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) to pick that off [should be a breeze](https://regexr.com/3vgv4). – JNevill Sep 14 '18 at 17:22

0 Answers0