Prefixing text in one column with text from another

[zoso]

Hello

Column A has values X001 - X999 in cells X1:X999

Column B has text in the same cell range.

How do I prefix all the cells in the range in Column B with 001-999 (i.e. ignoring the X) and a single space.

e.g.

Before:
B1 = John Smith
B2 = Jack Jones

After:
B1 = 001 John Smith
B2 = 002 Jack Jones

I guess I don't even need to reference Column A, so whichever way is simplest would be great!

The only stipulation is that the result must be text.

Thank you

 

[JLGWhiz]

In C1

=Right(A1, 3)&" "&B1

worked for me. Assuming the data in column A always has only three numeric digits.
Drag down as needed.

 

[onlyadrafter]

Hello,

VBA Version to overwrite col B cells

Sub CONCAT_B()
    For MY_ROWS = 1 To Range("A" & Rows.Count).End(xlUp).Row
            Range("B" & MY_ROWS).Value = Format(MY_ROWS, "000" & " " & Range("B" & MY_ROWS).Value)
    Next MY_ROWS
End Sub

 

[Peter_SSs]

For a macro approach, replacing all the original data in column B at once & without the need for the reference column A, try this on a copy of your data.

Sub AddPrefix()
  Application.ScreenUpdating = False
  With Range("B2", Range("B" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(row(#),text(row(#)-" & .Row & "+1,""000 "")&#,"""")", "#", .Address))
  End With
  Application.ScreenUpdating = True
End Sub

For another formula approach, without column A

=TEXT(ROWS(B$2:B2),"000 ")&B2

 

[zoso]

Thanks ever so much, I'll try both.

Next part of my problem is batch renaming the jpg files column B relates to without spending days doing it manually.

I know this is an Excel board but is there any software that could 'batch' recognise the text in the file names and prefix them?

 

[zoso]

Many thanks guys - Peter, your code was the best of all, thank you too!

 

[Peter_SSs]

Next part of my problem is batch renaming the jpg files column B relates to without spending days doing it manually.

I know this is an Excel board but is there any software that could 'batch' recognise the text in the file names and prefix them?

To consider this, I would need to know more about what you have, where and exactly what you are trying to achieve.

Many thanks guys - Peter, your code was the best of all, thank you too!

You are welcome.

 

[zoso]

Peter

What I am trying to do is allow a third party to view a long list of jpg files in sequential order in Windows Explorer (which isn't the same as the order they were in).

The jpgs were displayed in an excel form and for this to happen, they were named exactly the same as the text in the original cells.

Thanks to your code, the cells are now ready for the jpgs to be displayed in the excel form just as they were, but they will only do so now if the jpg text matches the text in the relevant cell exactly.

So I now need a way of matching each of the 800 or so jpgs with the relevant cell in the range and renaming with the relevant numerical prefix from the cell range. This would be a real chore to do myself and I was hoping there was a way to automate this.

Thanks again!